Solved

SQL Syntax - Number of Days in Past 3 Months

Posted on 2014-02-28
5
240 Views
Last Modified: 2014-07-10
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
Comment
Question by:rustypoot
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39896444
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
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39897771
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
 

Author Comment

by:rustypoot
ID: 39901127
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39901722
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
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 500 total points
ID: 39901798
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question