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 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