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.

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;