SQL DATEDIFF

Summary: in this tutorial, you will learn how to use the SQL DATEDIFF() function to calculate the difference between two dates.

Syntax

To calculate the difference between two dates, you use the DATEDIFF()function. The following illustrates the syntax of the DATEDIFF() function in SQL Server:

DATEDIFF ( datepart , startdate , enddate )
Code language: SQL (Structured Query Language) (sql)

Arguments

datepart

The datepart is a part of the date in which you want the function to return. The following table illustrates the valid parts of date in SQL Server:

startdate, enddate

The startdate and enddate are date literals or expressions from which you want to find the difference.

Return

The DATEDIFF() function returns an integer value with the unit specified by the datepart argument.

Examples

The following example returns the number of year between two dates:

SELECT DATEDIFF(year,'2015-01-01','2018-01-01');
Code language: SQL (Structured Query Language) (sql)

Here is the result:

3
Code language: SQL (Structured Query Language) (sql)

To get the number of month or day, you change the first argument to month or day as shown below:

SELECT DATEDIFF(month,'2015-01-01','2018-01-01'),
       DATEDIFF(day,'2015-01-01','2018-01-01');
Code language: SQL (Structured Query Language) (sql)

The following shows the result:

m           d
----------- -----------
36          1096
Code language: SQL (Structured Query Language) (sql)

Notice that the DATEDIFF() function takes the leap year into account. As shown clearly in the result, because 2016 is the leap year, the difference in days between two dates is 2×365 + 366 = 1096.

The following example illustrates how to use the DATEDIFF() function to calculate the difference in hours between two DATETIME values:

SELECT DATEDIFF(hour,'2015-01-01 01:00:00','2015-01-01 03:00:00');
Code language: SQL (Structured Query Language) (sql)

The result is:

2
Code language: SQL (Structured Query Language) (sql)

Consider the following example:

SELECT DATEDIFF(hour,'2015-01-01 01:00:00','2015-01-01 03:45:00');
Code language: SQL (Structured Query Language) (sql)

It also returns two because the DATEDIFF() function returns an integer only. In this case, it truncated the minute part and only consider the hour part.

2
Code language: SQL (Structured Query Language) (sql)

The following example shows how to use the DATEDIFF() function to calculate the year of services of employees up to January 1st, 2018:

SELECT first_name,
         last_name,
         DATEDIFF(year, hire_date, '2018-01-01') year_of_services
FROM employees;
Code language: SQL (Structured Query Language) (sql)

DATEDIFF in MySQL

Unlike SQL Server, MySQL has a slightly different DATEDIFF() function syntax:

DATEDIFF(startdate,enddate)
Code language: SQL (Structured Query Language) (sql)

MySQL only returns the difference between two dates in days. It ignores all the time part of the date in the calculation. See the following example:

SELECT DATEDIFF('2018-08-09','2018-08-18');
Code language: SQL (Structured Query Language) (sql)

The result is nine days:

9
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL DATEDIFF() function to calculate the difference between two dates.