Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has average 150 to 153 days. I think it should count up to 3rd Sept 2013.

I am using DATEDIFF(MONTH, VisitDate , GETDATE()) > =5

Should i use DATEDIFF( DAY , VisitDate , GETDATE()) >= 153 instead

share|improve this question
2  
what if you try this -- VisitDate <= DATEADD(MONTH,-5,GETDATE()); -- instead. –  DenisT Feb 3 at 16:48
    
Can you please clarify whether you want to include all the data from September 3rd in the results? Also, what do you want to do when the query is run on the 31st of a month, when 5 months earlier could not fall on the 31st? For example, 5 months before 2014-07-31 might be February 28th, or...? –  Aaron Bertrand Feb 3 at 17:32

3 Answers 3

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT 
  DATEDIFF(YEAR,'20120101','20131231'), 
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'
share|improve this answer

Edit: This solution is only applicable if you're working with DATE rather than DATETIME or DATETIME2 data types, though the advice still stands. For a more robust solution, see @Aaron Bertrand's answer.

Why not use something like the following:

DECLARE @Today      DATE,
        @MonthsBack INTEGER;
    SET @Today = GETDATE();
    SET @MonthsBack = 5;

SELECT  1
FROM    dbo.[foo]
WHERE   VisitDate <= DATEADD( MONTH, -@MonthsBack, @Today );

Datemath can be tricky enough without including magic numbers like 153.

share|improve this answer
    
This works only if VisitDate is DATE. If it has more granularity, you'll miss rows on September 3rd (unless they are stamped at midnight). From the question it seems the OP wants to include all data from September 3rd. If they don't want to include the 3rd, then it should be < rather than <=, so that you don't inadvertently include any data from the 3rd that doesn't have a time component. –  Aaron Bertrand Feb 3 at 17:29
    
Absolutely correct; edited to indicate the solution you've posted is superior. I just wished to point out the thinking behind taking the 153 route more than anything else, tbh. –  Avarkx Feb 3 at 17:38
    
Oh no problem, I wasn't being critical, just wanted to make sure that the differences were understood. –  Aaron Bertrand Feb 3 at 17:40
    
visitDate has DateTime parameter, it would be great if we count time as well if not then it is okay. –  user1263981 Feb 3 at 17:50
    
@AaronBertrand No worries at all, you didn't come off critical in the least. @user1263981, to compensate for the DATETIME datatype ( parameter doesn't seem like it's the correct term here ), see my edit and refer to @AaronBertrand's answer - the second pseudo code listing is exactly what you are looking for. –  Avarkx Feb 3 at 18:06

The DATEDIFF function does not calculate the difference in months based on days. It uses month boundaries as calculating the difference in months, with each change in calendar month adding one to the answer.

For example, in February 2014, the following returns 1, because January was the most recent calendar month:

SELECT DATEDIFF(MONTH, '20140130', GETDATE())

For actual 'month' differences that you're looking for, something like the following answer on Stack Overflow would probably help

Calculating number of full months between two dates in SQL

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.