mm/dd/yyyy hh:mm:ss - Standard DateTime value
08/27/2002 10:30:00 - Example

From the SQL Server Books on Line

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates. 

Syntax
DATEDIFF ( datepart , startdate , enddate ) 

Arguments
datepart

Is the parameter that specifies on which part of the date to calculate the difference. The table lists
dateparts and abbreviations recognized by Microsoft® SQL Server™.

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 


startdate

Is the beginning date for the calculation. startdate is an expression that returns a datetime or 
smalldatetime value, or a character string in a date format. 


Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and 
milliseconds are always 0.

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 time values, see Time Formats. For more information about specifying 
dates, see datetime and smalldatetime. 


enddate


Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime 
value, or a character string in a date format.


Return Types
integer


Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.


DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum 
number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.


The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given 
by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of 
datepart boundaries crossed between the first and second date. For example, the number of weeks between 
Sunday, January 4, and Sunday, January 11, is 1.


Examples
This example determines the difference in days between the current date and the publication date for titles 
in the pubs database.


USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO