DATEDIFF (TSQL Function)

For year, quarter, month, dayofyear, day, week, returns the count of specified boundaries that exist between startdate and enddate.
Boundaries are a regular series of tick marks on the time axis.

For hour, minute, second, millisecond, microsecond, nanosecond, returns the time interval between startdate and enddate, expressed in the specified unit.

Syntax

DATEDIFF ( datepart , startdate , enddate )

Arguments

datepart: type of the boundaries, or result unit.
See the list of valid values for datepart in the table below.
startdate: is the start date, of type date, time, datetime.
enddate: is the end date, of type date, time, datetime.

Return Types

Returns int.

Remarks

List of valid values for datepart:

datepart	Abbreviations	Remarks
year	yy , yyyy	boundaries are each January 1st, at 00:00:00
quarter	qq , q	boundaries are January 1st, April 1st, July 1st, October 1st, at 00:00:00
month	mm , m	boundaries are first day of each month, at 00:00:00
dayofyear	dy , y	same as day
day	dd , d	boundaries are start of each day, at 00:00:00
week	wk , ww	boundaries are each Saturday, at 00:00:00
hour	hh	real time interval is returned, as hours
minute	mi , n	real time interval is returned, as minutes
second	ss , s	real time interval is returned, as seconds
millisecond	ms	real time interval is returned, as milliseconds
microsecond	mcs	real time interval is returned, as microseconds
nanosecond	ns	real time interval is returned, as nanoseconds

Abbreviations can be used instead of the datepart full name. E.g. second, ss and s are equivalent.

The statement SET DATEFIRST has no effect on the boundaries, because DATEDIFF always uses Sunday as the week boundary.

If the return value is out of range for int [-2147483648, 2147483647], an error is raised.
The maximum time intervals between startdate and enddate are:

for minute, approximately 4085 years
for second, approximately 68 years.
for millisecond, approximately 24 days
for microsecond, approximately 35 minutes
for nanosecond, approximately 2 seconds

For hour, minute, second, etc, RSQL returns the real time interval expressed as hour, minute, second, etc.
MS SQL Server, however, returns the number of hour, minute, second, etc boundaries that have been crossed, which is less useful and unintuitive.

Examples

These examples return the count of specified boundaries between two dates:

PRINT DATEDIFF(year,    '2016-01-01', '2016-12-31') -- January 1st year boundary has not been crossed
PRINT DATEDIFF(year,    '2015-12-20', '2016-01-05') -- January 1st year boundary has been crossed
PRINT DATEDIFF(quarter, '2016-03-27', '2016-04-02') -- April 1st, quarter boundary, has been crossed
PRINT DATENAME(weekday, '2016-02-07')               -- prints Sunday
PRINT DATEDIFF(week, '2016-02-06 23:59:59.999999', '2016-02-07') -- Sunday week boundary has been crossed

The result is:

0
1
1
Sunday
1

These examples return the real time interval between two dates, expressed in the specified unit:

PRINT DATEDIFF(hour,   '2016-04-10 13:15:00', '2016-04-10 14:30:00') -- time interval is 1 hour and 15 minutes
PRINT DATEDIFF(minute, '2016-04-10 13:15:00', '2016-04-10 14:30:00') -- time interval is 75 minutes

The result is:

1
75

Guide

Reference