SQL Syntax - Number of Days in Past 3 Months

rustypoot
rustypoot used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

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
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

Open in new window

Author

Commented:
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.
Top Expert 2010

Commented:
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)

Open in new window

Here's the formula from above using current date/time.  I had used the declare and select state so that you could play around with it if you chose.

DATEDIFF(DD,DATEADD(DD, -1 * (DATEPART(DD,DATEADD(MM,-3,GETDATE())) - 1),DATEADD(MM,-3,GETDATE())),DATEADD(DD, -1 * DATEPART(DD,GETDATE()),GETDATE())) + 1

Open in new window


LVBarnes

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial