DATEDIFF() Examples – MySQL

In MySQL, you can use the DATEDIFF() function to find the difference between two dates. The way it works is, you provide two arguments (one for each date), and DATEDIFF() will return the number of days between the two dates.

Examples below.

Syntax

First, here’s the syntax:

DATEDIFF(expr1,expr2)

Where expr1 is the first date, and expr2 is the second date.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT DATEDIFF('2020-10-30', '2020-10-01') AS 'Result';

Result:

+--------+
| Result |
+--------+
|     29 |
+--------+

In this example, the first date is later than the second date. In this case we get a positive return value.

Example 2 – Comparison with an Earlier Date

The first date doesn’t have to be a later date than the second date. You can use an earlier date for the first argument and it will return a negative value. If we swap those two arguments around, we get the following:

SELECT DATEDIFF('2020-10-01', '2020-10-30') AS 'Result';

Result:

+--------+
| Result |
+--------+
|    -29 |
+--------+

Example 3 – Datetime Values

When used with datetime values, only the date part is used to compare the dates. Example:

SELECT 
  DATEDIFF('2020-10-30 23:59:59', '2020-10-01') AS 'Result 1',
  DATEDIFF('2020-10-01 23:59:59', '2020-10-30') AS 'Result 2';

Result:

+----------+----------+
| Result 1 | Result 2 |
+----------+----------+
|       29 |      -29 |
+----------+----------+

Example 4 – Database Query

Here’s an example of using DATEDIFF() in a database query. In this example, I compare the payment_date column with today’s date (by using the CURDATE() function to return today’s date):

USE sakila;
SELECT
  DATE(payment_date) AS 'Date/Time',
  CURDATE(),
  DATEDIFF(payment_date, CURDATE()) AS 'Date'
FROM payment
WHERE payment_id = 1;

Result:

+------------+------------+-------+
| Date/Time  | CURDATE()  | Date  |
+------------+------------+-------+
| 2005-05-25 | 2018-06-25 | -4779 |
+------------+------------+-------+