Overview
The DATEDIFF function in SQL Server is used to calculate the difference between two dates. It has three parameters, interval, date1, and date2.
DATEDIFF returns an integer representing the difference between date1 and date2 with the unit specified by the interval. DATEDIFF function works in SQL Server (starting with 2008), Azure SQL Managed Instance, Azure SQL Database, Azure Synapse Analytic, and Analytics Platform System (PDW).
What is DATEDIFF() in SQL Server?
As the name suggests, the DATEDIFF function in SQL Server is used to calculate the difference between two dates.
DATEDIFF function works in SQL Server (starting with 2008), Azure SQL Managed Instance, Azure SQL Database, Azure Synapse Analytic, and Analytics Platform System (PDW).
Features Of DATEDIFF()
- The DATEDIFF function in SQL Server is used to calculate the difference between two dates.
- This function is classified as a Date Function.
- This function accepts three parameters interval, date1, and date2.
- This function can contain time in both the interval and the date value sections.
Syntax Of DATEDIFF() in SQL Server
Parameter of DATEDIFF() in SQL Server
DATEDIFF in SQL Server accepts three parameters,
Interval:
Interval is the part of the difference that has to be returned by the DATEDIFF function. It can be as given below:
| Value | Explaination |
|---|---|
| year, yyyy, yy | Specified Year |
| quarter, qq, q | Specified Quarter |
| month, mm, m | Specified month |
| dayofyear | Specified day of the year |
| day, dy, y | Specified day |
| week, ww, wk | Specified week |
| weekday, dw, w | Specified week day |
| hour, hh | Specified hour |
| minute, mi, n | Specified minute |
| second, ss, s | Specified second |
| millisecond, ms | Specified millisecond |
Let's understand how the interval is specified in the DATEDIFF function.
Let the dates be 2009/02/10 and 2015/02/10, and the interval be year. Then the DATEDIFF function will return 6, as the difference of year between the two dates is 6. The difference between the above dates is exactly 6 years, therefore if the month is passed as the interval, the DATEDIFF function will return 72 as 6 years contain 72 months.
Date1, Date2:
These are the dates from which the difference will be calculated by the DATEDIFF function.
Return Type Of DATEDIFF() In SQL Server
DATEDIFF in SQL Server returns an integer representing the difference between date1 and date2 with the unit specified by the interval.
If the returned result is out of range for 32-bit integer(-2,147,483,648 to +2,147,483,647), then DATEDIFF in SQL Server returns an error. Therefore in the above case, one should use the DATEDIFF_BIG() function.
Example of DATEDIFF() in SQL Server
Let's have some examples for the DATEDIFF function in SQL Server.
We will provide the dates to the function in the format YYYY/MM/DD HH:mm:ss.ms.
Passing Year as Interval
Output:
Passing Month as Interval
Output :
Passing Hour as Interval
Output:
Passing Second as interval
Output :
Conclusion
- The DATEDIFF function in SQL Server is used to calculate the difference between two dates.
- The DATEDIFF function accepts three parameters, interval, date1, and date2.
- This function returns an integer representing the difference between date1 and date2 with the unit specified by the interval.
- If the returned result is out of range for 32-bit integer (-2,147,483,648 to +2,147,483,647), then DATEDIFF in SQL Server returns an error.