DATEDIFF() Function in SQL Server

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

Features

• This function is used to find the difference between the two given date values.
• This function comes under Date Functions.
• This function can include time in the interval section and also in the date value section.

Syntax

DATEDIFF(datepart, Start_date, End_date);

Parameter

This method accepts three parameters as given below:

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

datepart	abbreviations
year	yyyy,yy,y
quarter	qq,q
dayofyear	dy
day	dd,d
week	ww,wk
weekday	dw,w
hour	hh
minute	mi,n
second	ss,s
millisecond	ms

• Start_date: The starting date of the interval.
• End_date: The ending date of the interval.

Return Type of DATEDIFF() is: int

Return Value: It returns the difference between the two specified dates.

Selecting columns from date1 and date2

CREATE TABLE Customer 
    (Start_date datetime2, End_date datetime2);  
    
INSERT INTO Customer(Date1, Date2)  
    VALUES ('2017-05-06 12:11:09', '2017-05-07 12:11:09');    

Output

Customer Table

Query

SELECT DATEDIFF(day, Start_date, End_date) 
    FROM Customer;

Output

1

Selecting User-Defined Variables From Date1 and Date2

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

Example

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, @Start_date, @End_date);

Output

58744178

Selecting Constants From Date1 and Date2

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

Query

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

Output

11

Query

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

Selecting Constants from Date1 and Date2 with Hours

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

Query

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

Output

16318

Selecting Constants from Date1 and Date2 with Negative Value

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

Query

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

Output

-3704

Application

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

• Date Interval Calculation: The number of specified date parts between two provided dates is found using the DATEDIFF() method.
• Date Part Flexibility: Years, months, days, hours, minutes, and seconds are among the date parts that are supported.
• Versatility in Date Comparisons: DATEDIFF() makes it possible to compare dates from several time periods or frames, which makes trend analysis easier.
• Simple Syntax and Implementation: The syntax of the function in SQL Server queries is simple to understand and apply.

Conclusion

A flexible function that can be used to calculate a range of date intervals is DATEDIFF(). Anyone who wants to work with dates in SQL Server will find it to be a helpful tool.