Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - DATEDIFF() - Calculating DATETIME Value Differences

By: FYIcenter.com

(Continued from previous topic...)

How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?

If you want to calculate the difference between two date and time values, you can use the DATEDIFF() function in the following format:

DATEDIFF(datepart, startdate, enddate) returns INT: 
"startdate" - the start date
"enddate" - the end date
"datepart" - one of predefined date part names
   See other tutorials for the list of names

Note that SQL Server manual says DATEDIFF() returns the number of boundaries crossed by the specified part between two specified dates. This can be interpreted as truncating all parts smaller than the specified part on both input date and time values, then calculating the difference. The following tutorial exercise shows you some good DATEDIFF() usage examples:

-- Difference in days - truncating all parts
--    smaller than day
DECLARE @birth_date DATETIME;
DECLARE @today_date DATETIME;
SET @birth_date = '2000-02-29 16:10:00.000';
SET @today_date = '2000-03-02 18:30:00.000';
SELECT DATEDIFF(day, @birth_date, @today_date);
GO
2

-- Difference in hours - truncating all parts
--    smaller than hour
DECLARE @birth_date DATETIME;
DECLARE @today_date DATETIME;
SET @birth_date = '2000-02-29 16:10:00.000';
SET @today_date = '2000-03-02 18:30:00.000';
SELECT DATEDIFF(hour, @birth_date, @today_date);
GO
50

(Continued on next topic...)

  1. How To Add or Remove Days on Date and Time Values?
  2. Can Date and Time Values Be Converted into Integers?
  3. Can Integers Be Converted into Date and Time Values?
  4. Are DATETIME and NUMERIC Values Convertible?
  5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
  6. What Are the Date and Time Functions Supported by SQL Server 2005?
  7. How To Increment or Decrement Parts of DATETIME Values?
  8. How To Use DATEADD() Function?
  9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
  10. How To Calculate Age in Days, Hours and Minutes?
  11. How To Get Month and Weekday Names from DATETIME Values?
  12. How To Get Parts of DATETIME Values as Integers?
  13. How To Get Year, Month and Day Out of DATETIME Values?
  14. What Is the Difference Between GETDATE() and GETUTCDATE()?
  15. How To Format Time Zone in +/-hh:mm Format?
  16. How To Format DATETIME Values to Strings with the CONVERT() Function?
  17. How To Truncate DATETIME Values to Dates without Time?
  18. How To Set Different Parts of a DATETIME Value?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...