DATEDIFF() Function in SQL Server

DATEDIFF() function :
This function in SQL Server is used to find the difference between the two specified dates.

Features :

• This function is used to find the difference between the two given dates values.
• This function comes under Date Functions.
• This function accepts three parameters namely interval, first value of date, and second value of date.
• This function can include time in the interval section and also in the date value section.

Syntax :

DATEDIFF(interval, date1, date2)

Parameter :
This method accepts three parameters as given below :

• interval : It is the specified part which is to be returned. Moreover, the values of the interval can be as given below:

1. year, yyyy, yy = Year, which is the specified year.
2. quarter, qq, q = Quarter, which is the specified quarter.
3. month, mm, m = month, which is the specified month.
4. dayofyear, dy, y = Day of the year, which is the specified day of the year.
5. day, dd, d = Day, which is the specified day.
6. week, ww, wk = Week, which is the specified week.
7. weekday, dw, w = Weekday, which is the specified week day.
8. hour, hh = hour, which is the specified hour.
9. minute, mi, n = Minute, which is the specified minute.
10. second, ss, s = Second, which is the specified second.
11. millisecond, ms = Millisecond, which is the specified millisecond.

• date1, date2 : The two specified dates in order to find the difference between them.

Returns :
It returns the difference between the two specified dates.

Example-1 :
Using DATEDIFF() function and getting the difference between two values of dates, in years.

SELECT DATEDIFF(year, '2010/01/12', '2021/01/12');

Output :

11

Example-2 :
Using DATEDIFF() function and getting the difference between two values of dates, in months.

SELECT DATEDIFF(month, '2010/2/12', '2021/12/12');

Output :

142

Example-3 :
Using DATEDIFF() function and getting the negative difference between the two values of dates, in day.

SELECT DATEDIFF(day, '2021/2/1', '2010/12/12');

Output :

-3704

Example-4 :
Using DATEDIFF() function and getting the difference between the two values of dates which includes time as well, in hour.

SELECT DATEDIFF(hour, '2019/2/1 09:55', '2020/12/12 07:45');

Output :

16318

Example-5 :
Using DATEDIFF() function and getting the difference between the two values of dates using variables which includes time as well, in second.

DECLARE @date1 VARCHAR(50);
DECLARE @date2 VARCHAR(50);
SET @date1 = '2019/2/1 09:55:44';
SET @date2 = '2020/12/12 07:45:22';
SELECT DATEDIFF(second, @date1, @date2);

Output :

58744178

Application :
This function is used to find the difference between two specified values of date.

My Personal Notes arrow_drop_up

Save