DATEDIFF Function in SQL Server

In this SQL Server tutorial, you will learn about the DATEDIFF function in SQL Server. This function helps find the difference between two dates.

You will understand the ‘What is DATEDIFF() function with syntax’, then see how the DATEDIFF() function works with an example.

Ultimately, you will use the DATEDIFF() function on the tables to compute the year difference between two dates.

DATEDIFF Function in SQL Server

The DATEDIFF function in SQL Server finds the difference between two dates, which means if you need to know the number of days, year, month, etc, between two dates, the DATEDIFF() function can return those number of days, year, month.

Using this function, you can analyze the time-dependent date.

The syntax is given below.

DATEDIFF ( datepart , startdate , enddate )

Where,

• DATEDIFF(): The function returns the difference between two dates.

• datepart: It is part of the date to compute the difference. It can be a date and time component such as year, month, day, quarter, hour, minute, second, etc.
• start date: This is the starting date from where the calculation begins.
• enddate: This is the ending date on which the calculation stops.

The return type of the DATEDIFF function in SQL Server is int, which means it counts the differences of the year, month, day, etc, between two dates. The return type of the value is integer.

For the datepart, use the following date and time components:

Let’s begin with an example. You have two dates, ‘2024-01-04 14:33:05.74739383’ and ‘2025-06-05 18:10:10.2958739’.

First, find the year difference between both dates. So, use the query below.

SELECT DATEDIFF(year, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS YearDifference;

As you can see in the above output, the year difference is 1 between the two dates. Here in the query, datepart is the year which is why the DATEDIFF() function returns the year’s count between two dates.

Next, find the months between the two dates using the query below.

SELECT DATEDIFF(month, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS YearDifference;

As you can see, the month difference is 17 between both dates, the same as you count the difference of days, hours, minutes, and seconds. For that, use the query below.

SELECT DATEDIFF(day, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS DayDifference,
	  DATEDIFF(hour, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS HourDifference,
	  DATEDIFF(Minute, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS MinuteDifference,
	  DATEDIFF(second, '2024-01-04 14:33:05.74739383',
	  '2025-06-05 18:10:10.2958739') AS SecondDifference;

As you can see in the output of the above query, the DATEDIFF() function returns the number of days, hours, minutes, and seconds between two dates.

For example, the difference between days is 518, hours is 12436, minutes is 746137, and seconds is 44768225.

So to get the difference between two dates, you need to provide three values to the DATEDIFF() function: first, the interval or unit, which can be the datepart or datetime component. The second is the starting date, and the third is the ending date.

This is how to find the difference between two dates using the DATEDIFF function in SQL Server.

Using the DATEDIFF Function in SQL Server on Table Columns

This section will teach you how to use the DATEDIFF() function on the table column. For example, you have table Employees with columns EmployeeID, EmployeeName, and DateOfJoining shown below.

Using the above table, you must find how many years each employee has been with the company. You can know that by using the DATEDIFF function in SQL Server, as shown in the query below.

SELECT EmployeeName, 
DATEDIFF(year, DateOfJoining, GETDATE()) AS EmpeTenureInYears
FROM Employees;

As you can see in the above output, the result set contains two columns. First is the EmployeeName, which includes each employee’s name, and the second is EmpeTenureInYears, which includes how many years each employee has been with the company.

For example, Amelia has been working for the company for 11 years, Maverick for 12 years, Jackson for 4 years and so on for other employees.

Here, in the query part DATEDIFF(year, DateOfJoining, GETDATE()), the year is the interval, DateOfJoining is the columns containing the employee joining date, and GETDATE() is a function which returns the current date.

So, the query computes the year difference between the date of joining and the current date. This is how to use the DATEDIFF function in SQL Server on table column.

Using SUBSTRING Function in SQL Server with Variables

If you are working with variables in SQL Server or have dates stored in a variable, you can use those variables to compute the differences between two dates using the DATEDIFF function in SQL Server.

For example, execute the query below, which contains two variables containing date values.

DECLARE @startdate DATETIME2 = '2024-01-05 14:09:05.3112822';  
DECLARE @enddate   DATETIME2 = '2024-09-04 10:10:10.1112722';   
SELECT DATEDIFF(month, @startdate, @enddate) AS MonthDifference;

From the above output, you can see the month difference between the two dates is 8, but here, we haven’t specified dates directly in the SUBSTRING() function; instead, we have passed the two variables, @startdate and @enddate of type DATETIME2.

Both variables contain the date values, such as @startdate contains date as ‘2024-01-05 14:09:05.3112822’, and @enddate contains the date as ‘2024-09-04 10:10:10.1112722’.

You can use this function in your stored procedure containing many variables.

• Remember, if the provided end date is earlier than the start date, the DATEDIFF() function returns a negative result. Also, make sure that the data type for the start and end dates is compatible with the datetime data type.

This is how to use the DATEDIFF function in SQL Server with variable.

Conclusion

In this SQL Server tutorial, you learned how to compute the difference between two dates using the DATEDIFF function in SQL Server. Then, you applied the DATEDIFF() function on the table to find how many ears each employee has been with the company.

You may like to read:

• MONTH Function in SQL Server
• How to use SQL Server DATE_BUCKET Function
• DATEPART Function in SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.