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
   SELECT DATEDIFF(dd, GETDATE() - 1, GETDATE());
   -- Returns: 1
 
   -- The number of seconds in 24 hours
   SELECT DATEDIFF(ss, GETDATE() - 1, GETDATE());
   -- Returns: 86400

SQL Server DATEDIFF function:

Version: Microsoft SQL Server 2008 R2

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:

PostgreSQL:

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

Related datetime functions in SQL Server:

• SQL Server Articles and Reference

SQL Server 2008 R2 Books Online

• DATEDIFF Function