MySQL DATEDIFF() Function

In MySQL, the DATEDIFF() function returns the number of days between two date values.

`DATEDIFF()` Syntax

Here is the syntax of MySQL DATEDIFF() function:

DATEDIFF(date1, date2)

Parameters

date1: Required. A date or datetime expression.
date2: Required. A date or datetime expression.

Return value

The MySQL DATEDIFF() function returns the number of days between two date values. The function only compares the date parts of date1 and date2. It returns an positive integer if date1 is later than date2, otherwise it returns a negative integer or 0.

If the specified expression is not a valid date or datetime, the function will return NULL.

The function will return NULL if the argument is NULL.

`DATEDIFF()` Examples

Here are some examples of the DATEDIFF() function.

Simple usage

SELECT
    DATEDIFF('2022-02-28', '2022-02-28'),
    DATEDIFF('2022-02-28', '2022-02-28 10:10:10'),
    DATEDIFF('2022-02-28 10:10:10', '2022-02-28'),
    DATEDIFF('2022-02-28', '2022-02-27'),
    DATEDIFF('2022-02-28', '2022-02-27 10:10:10'),
    DATEDIFF('2022-02-28 10:10:10', '2022-02-27'),
    DATEDIFF('2022-02-30', '2022-02-28 10:10:10'),
    DATEDIFF('Not A DATEDIFF', 'Not A DATEDIFF'),
    DATEDIFF(NULL, '2022-02-28')\G

         DATEDIFF('2022-02-28', '2022-02-28'): 0
DATEDIFF('2022-02-28', '2022-02-28 10:10:10'): 0
DATEDIFF('2022-02-28 10:10:10', '2022-02-28'): 0
         DATEDIFF('2022-02-28', '2022-02-27'): 1
DATEDIFF('2022-02-28', '2022-02-27 10:10:10'): 1
DATEDIFF('2022-02-28 10:10:10', '2022-02-27'): 1
DATEDIFF('2022-02-30', '2022-02-28 10:10:10'): NULL
 DATEDIFF('Not A DATEDIFF', 'Not A DATEDIFF'): NULL
                 DATEDIFF(NULL, '2022-02-28'): NULL

Get the number of days since a date

SELECT DATEDIFF(NOW(), '2022-02-28');

+-------------------------------+
| DATEDIFF(NOW(), '2022-02-28') |
+-------------------------------+
|                            44 |
+-------------------------------+

Here, we used the NOW() function to get the current moment. You can also use CURDATE(), CURRENT_DATE(), or SYSDATE(). For example:

SELECT
    DATEDIFF(NOW(), '2022-02-28'),
    DATEDIFF(CURDATE(), '2022-02-28'),
    DATEDIFF(CURRENT_DATE(), '2022-02-28'),
    DATEDIFF(SYSDATE(), '2022-02-28')\G

         DATEDIFF(NOW(), '2022-02-28'): 44
     DATEDIFF(CURDATE(), '2022-02-28'): 44
DATEDIFF(CURRENT_DATE(), '2022-02-28'): 44
     DATEDIFF(SYSDATE(), '2022-02-28'): 44

DATEDIFF() Syntax