SQL Server - DATEDIFF - Datetime Difference in Seconds, Days, Weeks etc

SQL Server DATEDIFF function returns the difference in seconds, minutes, hours, days, weeks, months, quarters and years between 2 datetime values.

Quick Example:

   -- The difference is days between today and yesterday
   -- Returns: 1
   -- The number of seconds in 24 hours
   -- Returns: 86400


SQL Server DATEDIFF function:

Syntax DATEDIFF(units, start_date, end_date)
Calculation Units mcs microsecond Microseconds
ms millisecond Milliseconds
ss s second Seconds
mi n minute Minutes
hh hour Hours
dd d day Days
wk ww week Weeks
mm m month Months
qq q quarter Quarters
yyyy yy year Years
Return Type INTEGER
Accuracy Does not guarantee that the full amount of units passed between datetimes
Negative Value Returned is start_date is later than end_date

Version: Microsoft SQL Server 2008 R2

SQL Server DATEDIFF Function Details

DATEDIFF does not guarantee that the full number of the specified time units passed between 2 datetime values:

   -- Get difference in hours between 8:55 and 11:00
   SELECT DATEDIFF(hh, '08:55', '11:00');
   -- Returns 3 although only 2 hours and 5 minutes passed between times
   -- Get difference in months between Sep 30, 2011 and Nov 02, 2011
   SELECT DATEDIFF(mm, '2011-09-30', '2011-11-02')
   -- Returns 2 although only 1 month and 2 days passed between dates

To get the number of full time units passed between datetimes, you can calculate the difference in lower units and then divide by the appropriate number:

   SELECT DATEDIFF(mi, '08:55', '11:00')/60;
   -- Returns 2 hours now

SQL Server DATEDIFF Conversion to Other Databases

SQL Server DATEDIFF conversion:


PostgreSQL does not provide DATEDIFF function, but you can use various datetime expressions or a user-defined function (UDF) to get the same functionality:

   -- Get difference in hours between 8:55 and 11:00
   SELECT DATE_PART('hour', '11:00'::time - '08:55'::time);

For more information including a UDF example, see How to Implement DATEDIFF in PostgreSQL

Datetime Functions in SQL Server

Related datetime functions in SQL Server:

DATEPART(part, datetime) Returns the specified part of datetime value
YEAR(datetime) Returns the year of datetime value
MONTH(datetime) Returns the month of datetime value
DAY(datetime) Returns the day of datetime value


SQL Server 2008 R2 Books Online