Why am I getting wrong results with datediff with MSSQL

SELECT DATEDIFF(MONTH,'20070530','20070702') AS 'Month',
       DATEDIFF(week,'20070530','20070702') AS 'Weeks',
       DATEDIFF(DAY,'20070530','20070702') AS 'Days';
go
Month       Weeks       Days
----------- ----------- -----------
2           5           33

(1 row(s) affected)
  1. According to Books On Line:
    Datediff... Returns the number of date and time boundaries crossed between two specified dates.
  2. Datediff only cares about the boundaries, see the next example.
SELECT DATEDIFF(MONTH,'20070531','20070601') AS 'Month',
       DATEDIFF(week,'20070531','20070601') AS 'Weeks',
       DATEDIFF(DAY,'20070531','20070601') AS 'Days';
go
Month       Weeks       Days
----------- ----------- -----------
1           0           1

(1 row(s) affected)
  • Although it is just a 1 day, it's reported as a month because it crossed the month boundary
  • Is this what you actually want? Maybe yes, maybe no, ie: Accounting: AR30/60...: no, Month End: yes
SELECT DATEDIFF(YEAR,'20061230','20070102') AS 'Year',
       DATEDIFF(MONTH,'20061230','20070102') AS 'Month',
       DATEDIFF(week,'20061230','20070102') AS 'Weeks',
       DATEDIFF(DAY,'20061230','20070102') AS 'Days';
go
Year        Month       Weeks       Days
----------- ----------- ----------- -----------
1           1           1           3

(1 row(s) affected)