Solved

SQL Syntax - Number of Days in Past 3 Months

Posted on 2014-02-28
5
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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