What is SQL DATEDIFF function?

The DATEFIDFF is a function available in SQL databases like SQL Server, MySQL etc. In this tutorial, you will learn how to use DATEDIFF in SQL Server as there are differences while using this function in different databases.

For learning how to use DATEDIFF in MySQL, go to its tutorial here.

In MS SQL Server, the DATEDIFF function is used to get the difference between two dates in terms of years, months, days, hours, minutes etc.

For example:

1	SELECT DATEDIFF(day, '2018-03-13', GETDATE()) AS "Difference in days";

On that basis, let me explain the three arguments required in the DATEDIFF function syntax.

The Day specified the datepart/interval. The interval or datepart argument can be year, month, day etc. See the available list in the last section, after examples.
The second argument is the Start Date.
Third argument is the Ending Date that you want to calculate the difference for the specified interval. I used GETDATE() function there which means use the current system date.

The example of getting years between two dates

In this example, we will get the years between two dates by using DATEDIFF SQL function. The start date is a constant value while the end date is again system’s current time (assume this is 2018). See the query and result:

The query with DATEDIFF:

SELECT DATEDIFF(year, '2010-03-13', GETDATE())

AS "Years Difference";

The result:

Getting the number of months difference

Similarly, you may use the month as the interval for getting the number of months between two given dates. Have a look at its usage and result by same two dates:

SELECT DATEDIFF(month, '2010-03-13', GETDATE())

AS "Month Difference";

The result assuming the current month/year is 04/2018:

Getting days between two dates example

For the number of days, I am using two constant dates for the first and last date arguments. For the interval argument, use the day value as follows:

The query for calculating days difference:

SELECT DATEDIFF(day, '2017-04-13', '2018-04-13')

AS "Number of Days";

The result:

Getting days, months and years difference with tabular data

Now see how DATEDIFF function is used with the table column’s date column for calculating the difference in terms of days, months and years.

For that, I am using the sto_employees table that stores information about the employees including joining date. By using DATEDIFF function, we will calculate the duration for each employee in the table. See the table before and after using DATEDIFF query:

SELECT *, GETDATE(),

DATEDIFF(day, join_date, GETDATE()) AS " Number of Days ",

DATEDIFF(month, join_date, GETDATE()) AS " Number of Months ",

DATEDIFF(year, join_date, GETDATE()) AS " Number of Years "

FROM sto_employees;

You can see, the current date is displayed in a separate column for each employee by using the GETDATE() function. After that, this date is subtracted to join date for getting the number of days, months and years.

Calculating Hours, Minutes and Seconds between two dates

Using the same table data as in above example, we will calculate the hours, minutes and seconds and display it with each employee’s record.

The query:

SELECT *, GETDATE() AS "Current Date",

DATEDIFF(HOUR, join_date, GETDATE()) AS " Hours ",

DATEDIFF(MINUTE, join_date, GETDATE()) AS " Minutes ",

DATEDIFF(SECOND, join_date, GETDATE()) AS " Seconds "

FROM sto_employees;

The resultset:

List of valid datepart/interval argument

The following values can be used as the datepart argument in the DATEDIFF function for getting days, years, months, weekdays etc. See the datepart and their abbreviation below:

For Year:

year, yy, yyyy

Month:

month, mm, m

Day of Year:

dayofyear, dy, y

Day:

day, dd, d

Week:

week, wk, ww

Hour:

hour, hh

Minute:

minute, mi, n

Second:

second, ss, s

Milliseconds

milliseconds, ms