Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
rustypoot
Asked:
rustypoot
1 Solution
 
David ToddSenior DBACommented:
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
0
 
Lawrence BarnesCommented:
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

0
 
rustypootAuthor 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.
0
 
Patrick MatthewsCommented:
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

0
 
Lawrence BarnesCommented:
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now