DATEDIFF
Returns the count of a specified datepart (e.g. day, month, year) between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )Parameters
| Parameter | Description | Data Type |
|---|---|---|
datepart |
Specifies the part of the date that is returned. The following values are recognized: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, nanosecond. |
VARCHAR |
startdate |
The start date of the interval. Can be a valid date or datetime expression. | DATE, DATETIME, SMALLDATETIME, DATETIME2, DATE |
enddate |
The end date of the interval. Can be a valid date or datetime expression. | DATE, DATETIME, SMALLDATETIME, DATETIME2, DATE |
Return Value
An integer that represents the number of datepart boundaries crossed between startdate and enddate.
Remarks
DATEDIFFcounts the number of specifieddatepartboundaries crossed between the two dates.- For
week,DATEDIFFuses the system setting for the first day of the week. - If
startdateis later thanenddate,DATEDIFFreturns a negative value. - If any of the date parameters are
NULL,DATEDIFFreturnsNULL.
Examples
Example 1: Calculate the difference in days between two dates.
SELECT DATEDIFF(day, '2023-01-01', '2023-01-15');
-- Result: 14Example 2: Calculate the difference in months.
SELECT DATEDIFF(month, '2023-01-15', '2023-04-10');
-- Result: 3Example 3: Calculate the difference in hours.
SELECT DATEDIFF(hour, '2023-01-15 08:00:00', '2023-01-15 17:30:00');
-- Result: 9Example 4: Using variables.
DECLARE @StartDate DATE = '2023-05-01';
DECLARE @EndDate DATE = '2023-08-20';
SELECT DATEDIFF(quarter, @StartDate, @EndDate);
-- Result: 2