DATEDIFF (Transact-SQL)

Returns the count of the specified datepart boundaries between two specified dates.

Syntax

DATEDIFF ( datepart , startdate , enddate )

Parameters

Parameter Description
datepart The part of the date that is returned by the function. The following values are valid for the datepart argument.
startdate The starting date of the interval. startdate must be a valid date or datetime expression.
enddate The ending date of the interval. enddate must be a valid date or datetime expression.

Return Value

An integer representing the number of datepart boundaries crossed between startdate and enddate. The return value is a signed integer. The sign depends on whether enddate is earlier or later than startdate.

Supported datepart Values

The following table lists the valid arguments for the datepart parameter.

Abbreviation datepart Alias Range
yy year yyyy, yy 1753 through 9999
qq quarter qq, q 1 through 4
mm month mm, m 1 through 12
yy year yyyy, yy 1753 through 9999
dd day dd, d 1 through 31
wk week wk, ww 1 through 53
dw dayofyear dayofyear, dy, y 1 through 366
hh hour hh, h 0 through 23
mi minute mi, n 0 through 59
ss second ss, s 0 through 59
ms millisecond ms 0 through 999
mcs microsecond mcs 0 through 999999
ns nanosecond ns 0 through 999999999

Examples

Example 1: Calculating the difference in days

SELECT DATEDIFF(day, '2023-10-26', '2023-11-10');

This will return 15.

Example 2: Calculating the difference in months

SELECT DATEDIFF(month, '2023-01-15', '2023-03-20');

This will return 2.

Example 3: Calculating the difference in years

SELECT DATEDIFF(year, '2020-07-01', '2023-01-01');

This will return 3.

Example 4: Using variables

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-06-15';
SELECT DATEDIFF(week, @StartDate, @EndDate);

This will return 24.

Note: DATEDIFF counts the number of boundaries crossed. It does not return the total time elapsed divided by the datepart's length. For example, DATEDIFF(day, '2023-10-31 23:59:59', '2023-11-01 00:00:01') returns 1, even though only two seconds have passed.
Tip: When calculating the difference between two dates, ensure that the enddate is later than the startdate if you expect a positive result. If startdate is later than enddate, the result will be negative.

Related Topics