MariaDB DATEDIFF() Function

In MariaDB, DATEDIFF() is a built-in function that returns the difference between two dates in days.

MariaDB DATEDIFF() Syntax

This is the syntax of the MariaDB DATEDIFF() function:

DATEDIFF(date1, date2)

Parameters

date1

Required. A date or datetime expression.

date2

Required. A date or datetime expression.

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'.

Return value

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

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

If the argument is NULL, the DATEDIFF() function will return NULL.

MariaDB DATEDIFF() Examples

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

Output:

         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

Compare a date and today

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

Output:

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

Here, we use the NOW() function to get the current moment. In addition to this, you can also use CURDATE(), CURRENT_DATE(), 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

Output:

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

Conclusion

In MariaDB, DATEDIFF() is a built-in function that returns the difference between two dates in days.