DATE_DIFF (Transact-SQL)

Returns the difference between two specified dates as an integer representing the specified datepart of that difference.

Syntax

Parameters

• datepart The part of the date that is returned. This parameter can be any of the valid datepart values that are listed in the Supported Datepart Values table.
• startdate A Transact-SQL expression that resolves to a date value, a datetime value, or a character string in a datetime format. The startdate must be less than or equal to the enddate.
• enddate A Transact-SQL expression that resolves to a date value, a datetime value, or a character string in a datetime format. The enddate must be greater than or equal to the startdate.

Return Value

An integer that represents the specified datepart difference between the startdate and enddate. If startdate is later than enddate, it returns a negative number.

Supported Datepart Values

The following are the valid values for the datepart argument:

year, yyyy, yy
quarter, qq, q
month, mm, m
dayofyear, dy, y
day, dd, d
week, wk, ww
hour, hh, h
minute, mi, n
second, ss, s
millisecond, ms
microsecond, mcs
nanosecond, ns
            

Examples

SELECT DATE_DIFF(year, '2023-01-01', '2024-12-31');
-- Result: 2
                

SELECT DATE_DIFF(day, '2024-01-01', '2024-03-15');
-- Result: 74
                

SELECT DATE_DIFF(hour, '2024-03-15 10:00:00', '2024-03-15 18:30:00');
-- Result: 8
                

DECLARE @StartDate DATE = '2023-05-10';
DECLARE @EndDate DATE = '2024-08-20';
SELECT DATE_DIFF(month, @StartDate, @EndDate) AS MonthDifference;
-- Result: 15