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.
DATEDIFF(datepart, startdate, enddate)
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.
|year||y, yy, yyyy|
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.
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
SELECT EmployeeID, FirstName, DATEDIFF(mm, HireDate, GETDATE()) AS TimeInMonths FROM Employee
Get the Difference between Times
DATEDIFF() function can also return the difference between the specified time values when datepart parameter is
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;