The SQL DATEDIFF function calculates and returns the difference between two date values. The value returned is an integer. You can use DATEDIFF to calculate a wide variety of calendar calculation by varying the datepart parameter.
Use SQL DATEDIFF to return the difference between the two dates based on a specified date part. The DATDIFF function returns this difference as an integer value.
DATADIFF ( datepart, startdate, enddate ).
SQL DATEDIFF Usage Notes
- The datepart as the first argument indicates the value type the SQL DATEDIFF function returns as a difference between the start date and the end date. The list of valid values for the date part argument includes a year, month, day, etc. These values can also be an abbreviation like yy for a year and mm for a month.
- Start date and end date as the final two arguments can be of date, datetime, datetimeoffset, datetime2, samlldatetime, and time data type.
- The return type of the DATEDIFF function is an int and indicates the difference between the start date and end date.
- If the date part is a date, then the SQL DATEDIFF function sets the time part of the date to 00:00:00. When specified as a time, then the DATEDIFF function sets the missing date part to 1900-01-01.
- If any of the start date and end date has more time parts than the other, DATADIFF sets the missing parts of smaller value to zero.
- The SQL DATADIFF function does not support the YDM format when date values are passed as strings because it casts the literal strings to the datetime2 data type. Therefore, date values must be first converted to datetime or smalldatetime data types instead of passing as string values.
SQL DATEDIFF Examples
We will use the Adventureworks2019 database for the following queries.
/* Answer */ SELECT DATEDIFF(MINUTE, '2021/05/01 06:00', '2021/05/01 11:14') AS DateDiffResult;
The following example is a simple SQL DATEDIFF query to calculate the difference between two date values in terms of minutes.
In the next example, we calculate the days left in the order delivery by applying the SQL DATEDIFF function to the OrderDate and DueDate columns of the SalesOrderHeader table.
/* Answer */ SELECT SalesOrderID, OrderDate, DueDate, DATEDIFF(day, OrderDate, DueDate) DaysLeft FROM Sales.SalesOrderHeader
Next, let’s apply the SQL DATEDIFF function with an aggregate function. For every job title, the query calculates the maximum number of years an employee has worked.
/* Answer */ SELECT JobTitle, DATEDIFF(year, MAX(HireDate), SYSDATETIME()) AS YearsEmployed FROM HumanResources.Employee GROUP BY JobTitle
The above query displays the job title and the maximum number of years an employee has worked under this job title. MAX function selects the maximum date value against each job title as indicated by the Group By clause. SYSDATETIME returns the current time. Here, SQL DATEDIFF calculates the difference between the hired date and the current time.