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
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'
Edit: This solution is only applicable if you’re working with
DATE rather than
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
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