rustypoot
asked on
SQL Syntax - Number of Days in Past 3 Months
Hello,
I am working on a report. I need to use number of days in Previous 3 Months for one of the formulas.
For Example, if I run the report in Feb, the the number of days in previous 3 months will be 92 (Nov - 30, Dec - 31, Jan - 31). If I run the report in March, the the number of days in previous 3 months will be 90 (dec - 31, jan - 31, feb - 28).
How can I accomplish this in SQL Server? Thanks
I am working on a report. I need to use number of days in Previous 3 Months for one of the formulas.
For Example, if I run the report in Feb, the the number of days in previous 3 months will be 92 (Nov - 30, Dec - 31, Jan - 31). If I run the report in March, the the number of days in previous 3 months will be 90 (dec - 31, jan - 31, feb - 28).
How can I accomplish this in SQL Server? Thanks
Seems like you may want the total number of days in the last three complete calendar months (vs rolling months.) Below is a query that show's the parts and has a single formula for your calc.
DECLARE @TargetDate DATE
SET @TargetDate = '2014-02-15'
SELECT @TargetDate as CurrentDate
, DATEADD(DD, -1 * (DATEPART(DD,DATEADD(MM,-3,@TargetDate)) - 1),DATEADD(MM,-3,@TargetDate)) as FirstDayofThreeMonthsAgo
, DATEADD(DD, -1 * DATEPART(DD,@TargetDate),@TargetDate) as LastDayofLastMonth
, DATEDIFF(DD,DATEADD(DD, -1 * (DATEPART(DD,DATEADD(MM,-3,@TargetDate)) - 1),DATEADD(MM,-3,@TargetDate)),DATEADD(DD, -1 * DATEPART(DD,@TargetDate),@TargetDate)) + 1 as NbrDaysLast3CompleteMonths
ASKER
Thank you. But I can not declare a specific date as start date; the report is run daily so the Target date will change daily. And the business wants exact Number of days.
Based on the current system date/time:
DECLARE @Start date = (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0)),
@End date = (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
SELECT DATEDIFF(day, @Start, @End)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd check with the business if that number needs to be exact. The usual number is 90 days.
try select datediff( day, '2013-11-01', '2014-02-01' )
Note that I've gone one day past the three months, but fairly certain the result will be correct.
HTH
David