Returns the number of date and time boundaries crossed between two specified dates.
DATEDIFF ( datepart , startdate , enddate )
- Is the parameter that specifies on which part of the date to calculate the difference. The following table lists dateparts and abbreviations recognized by Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE).
Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww hour hh minute mi, n second ss, s millisecond ms
- Is the beginning date for the calculation. startdate is an expression that returns a datetime value or a Unicode character string in a date format.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if the two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
For more information about specifying dates, see datetime in Data Types.
- Is the ending date for the calculation. enddate is an expression that returns a datetime value or a Unicode character string in a date format.
The following example uses the Northwind database. The example determines the number of days it took to ship the orders made by the company Vins et alcohols Chevalier, which has a CustomerID of VINET.
SELECT OrderID, DATEDIFF (d, OrderDate, ShippedDate) AS "Shipping Time in Days" FROM Orders WHERE CustomerID = 'VINET'
This is the result set:
OrderID Shipping Time in Days ------------------------------------- 10295 8 10737 7 10274 10 10248 12 10739 5