SQL Server DATEDIFF() Function
In SQL Server, the DATEDIFF() function returns the difference between the specified start date and end date in integer. It can be difference between days, months, weeks, hours, seconds based on the passed datepart parameter.
Syntax:
DATEDIFF(datepart, startdate, enddate)
Parameters
datepart: It is the part of the date like day, month, year, week, etc.
It is the unit in which the DATEDIFF() function returns the difference between a start date and an end date e.g. it returns the difference in days if datepart is day.
| Datepart | Datepart abbreviation |
|---|---|
| year | y, yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
startdate: The starting datetime value.
enddate: The ending datetime value.
Difference will be calculated as startdate - enddate.
startdate and enddate have to be in a format that can be resolved to types DATE, DATETIME, TIME, SMALLDATETIME, DATETIMEOFFSET.
Return Value
Returns an integer value, which is the difference between the specified start date and the end date.
Returns an error if the result is out of range for an integer (-2,147,483,648 to +2,147,483,647). Use the DATEDIFF_BIG() function to handle large difference between startdate and enddate values.
DATEDIFF() returns zero if both the start date and the end date are time values and the datepart is not a time datepart.
DATEDIFF() uses the time zone offset component of the startdate or enddate to calculate the return value.
Get Difference between Dates in Days
SELECT DATEDIFF(day, '01/10/2022 4:23:00', '01/11/2022 8:23:00') AS ReturnDate
In the below example, the startdate is bigger than the enddate, and so the DATEDIFF() function returns a negative value.
SELECT DATEDIFF(day, '12/23/22', '01/11/2022') AS ReturnDate
Use DATEDIFF() with Column
In the following example, DATEDIFF() function is used with the HireDate column of the Employee table and the GETDATE() function which returns a current datetime value.
It returns the time elapsed in months from the hire date of the employee along with EmployeeId and FirstName.
SELECT EmployeeID, FirstName, DATEDIFF(mm, HireDate, GETDATE()) AS TimeInMonths
FROM Employee
Get the Difference between Times
The DATEDIFF() function can also return the difference between the specified time values when datepart parameter is HH, MI, and SS.
select datediff(HH,'3:22:59','4:23:50') as HoursDiff,
datediff(MI,'3:20:59','4:23:50') as MinsDiff,
datediff(SS,'4:22:59', '4:23:50') as SecDiff;
