Returns the difference between two dates.
datediff(datepart, date1, date2)
is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.
can be either the function getdate, a character string in an acceptable date format, an expression that evaluates to a valid date format, or the name of a datetime column.
can be either the function getdate, a character string in an acceptable date format, an expression that evaluates to a valid date format, or the name of a datetime or smalldatetime column.
This query finds the number of days that have elapsed between pubdate and the current date (obtained with the getdate function):
select newdate = datediff(day, pubdate, getdate()) from titles
datediff, a date function, calculates the number of date parts between two specified dates. For more information about date functions, see “Date functions”.
datediff takes three arguments. The first is a date part. The second and third are dates. The result is a signed integer value equal to date2 - date1, in date parts.
datediff produces results of datatype int, and causes errors if the result is greater than 2,147,483,647. For milliseconds, this is approximately 24 days, 20:31.846 hours. For seconds, this is 68 years, 19 days, 3:14:07 hours.
datediff results are always truncated, not rounded, when the result is not an even multiple of the date part. For example, using hour as the date part, the difference between “4:00AM” and “5:50AM” is 1.
When you use day as the date part, datediff counts the number of midnights between the two times specified. For example, the difference between January 1, 1992, 23:00 and January 2, 1992, 01:00 is 1; the difference between January 1, 1992 00:00 and January 1, 1992, 23:59 is 0.
The month datepart counts the number of first-of-the-months between two dates. For example, the difference between January 25 and February 2 is 1; the difference between January 1 and January 31 is 0.
When you use the date part week with datediff, you get the number of Sundays between the two dates, including the second date but not the first. For example, the number of weeks between Sunday, January 4 and Sunday, January 11 is 1.
If smalldatetime values are used, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are automatically set to 0 for the purpose of the difference calculation.
SQL92 – Compliance level: Transact-SQL extension.
Any user can execute datediff.
Datatypes Date and time datatypes
Commands select, where clause
Functions dateadd, datename, datepart, getdate