Solved

SQL Syntax - Number of Days in Past 3 Months

Posted on 2014-02-28
5
239 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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