DATEDIFF (Transact-SQL) showing wrong values
Today I was asked to change a query that was showing the result of last 2 weeks. I have to change the filter criteria of date from last 2 weeks to last 2 months as current query was written like this
where DateDIFF(ww, Employees.JoinDate,getDate())<=2
It was used for to get all employees have join date is less or equal to 2 weeks , so I just change the datepart from week to month as mm and chagne the above line to
AND DateDIFF(mm, Employee.StartDate,getDate())<=2 (I was thinking that it would work to get records for last 2 months ) but when I did some unit testting then I have come to know that it showing the wrong result even those records was also showing which date is more then last 2 months .Then I tried to check the behavior of datediff with different example and reading its documentation form online book (Returns the number of date and time boundaries crossed between two specified dates.) from msdn
The example I tried:
SELECT DATEDIFF(mm,'2011-05-01','2011-07-31') AS Months
Months
-----------
2
(1 row(s) affected)
And the same start and end date but using datepart day instead of month showing as
SELECT DATEDIFF(dd,'2011-05-01','2011-07-31') AS Days
Days
-----------
91
(1 row(s) affected)
It was showing 91 days but if we execute the query with month it showing 2 months so then I come to know that datediff only cares about boundaries as it was showing 91 days difference because 90 days has been passed but it only shows 2 month difference when using month as datepart because the month boundary is 2 as start date month is 5 and end date month is 7.
Let me show you another example
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2010-12-19' December 19 , 2011
SET @enddate = '2010-12-25' December 25 , 2011
SELECT DATEDIFF(dd,@startdate,@enddate) AS 'day',DATEDIFF(ww,@startdate,@enddate) AS 'Week'
day Week
----------- -----------
6 0
(1 row(s) affected)
It showing 0 week difference as week boundary did not pass yet and 19 and 25 are in same week of December 2010 that’s why it showing 0 week difference. See the December 2010 month screen shot
But If we changed the start date to 18 december 2011 and end date to 19 December 2011 it would show one week differece as these dates not fall in same week and difference is oen week.
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2010-12-18'
SET @enddate = '2010-12-19'
SELECT DATEDIFF(dd,@startdate,@enddate) AS 'day',DATEDIFF(ww,@startdate,@enddate) AS 'Week'
day Week
----------- -----------
1 1
(1 row(s) affected)
See here only 1 day difference and showing 1 week difference however in previous query it was showing only 0 week differce but we have 6 day difference this is because of boundaries that this function considering from datepar (i.e day or week).
Let see another example
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2010-12-31'
SET @enddate = '2011-01-1'
SELECT DATEDIFF(dd,@startdate,@enddate) AS 'day',DATEDIFF(ww,@startdate,@enddate) AS 'Week',DATEDIFF(mm,@startdate,@enddate) AS 'month',
DATEDIFF(yy,@startdate,@enddate) AS 'year'
day Week month year
----------- ----------- ----------- -----------
1 0 1 1
(1 row(s) affected)
Here result are very interesting as it shows 1 day difference but 0 week difference and more important 1 month and 1 year difference how this happened ?
Same logic as December 31, 2010 and January 01 , 2001 falls in same week so it did list 0 week difference but as month boundary crossed as start date month was December and enddate month is January hence it showed 1 month difference and same is the case for year becuae start date year is 2010 and end date year is 2011 hence it showed 1 year difference.
I hope now its clear the behavior of DateDiff function ,
So what I did for my query that need to show the record for last 2 months I just used like
where Employees.JoinDate >= DATEADD(m, -2, GETDATE())
