MySQL: DATEDIFF Function

This tutorial explains how to use the MySQL DATEDIFF function with syntax and examples.

Description

The MySQL DATEDIFF function returns the difference in days between two date values.

Syntax

The syntax for the DATEDIFF function in MySQL is:

DATEDIFF( date1, date2 )

Parameters or Arguments

date1 and date2

The two dates to calculate the difference between. The calculation is date1 - date2.

Note

• Only the date portion of date1 and date2 is used in the DATEDIFF calculation. The time portion of date1 and date2 is ignored.

Applies To

The DATEDIFF function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1.1

Example

Let's look at some MySQL DATEDIFF function examples and explore how to use the DATEDIFF function in MySQL.

For example:

mysql> SELECT DATEDIFF('2014-01-28', '2014-01-27');
Output: 1

mysql> SELECT DATEDIFF('2014-01-28 11:41:14', '2014-01-27 12:10:08');
Output: 1

mysql> SELECT DATEDIFF('2014-01-28 11:41:14', '2014-01-27');
Output: 1

mysql> SELECT DATEDIFF('2014-02-15', '2014-02-10');
Output: 5

mysql> SELECT DATEDIFF('2014-01-28', '2013-12-31');
Output: 28

mysql> SELECT DATEDIFF('2013-12-31', '2014-01-28');
Output: -28

This last DATEDIFF example would display the difference between current system date and '2014-02-14' (current system date is returned by the CURDATE function).

mysql> SELECT DATEDIFF(CURDATE(), '2014-02-14');