How DATEDIFF() Works in MariaDB

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

It accepts two arguments, both of which are date or datetime expressions. It then subtracts the second date from the first.

Syntax

The syntax goes like this:

DATEDIFF(expr1,expr2)

It then returns expr1 - expr2.

Example

Here’s an example:

SELECT DATEDIFF('2030-01-25', '2030-01-20');

Result:

+--------------------------------------+
| DATEDIFF('2030-01-25', '2030-01-20') |
+--------------------------------------+
|                                    5 |
+--------------------------------------+

Here it is again, but with the dates swapped around:

SELECT DATEDIFF('2030-01-20', '2030-01-25');

Result:

+--------------------------------------+
| DATEDIFF('2030-01-20', '2030-01-25') |
+--------------------------------------+
|                                   -5 |
+--------------------------------------+

Datetime Values

Here’s an example that uses datetime values:

SELECT DATEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59');

Result:

+--------------------------------------------------------+
| DATEDIFF('2030-01-25 00:00:00', '2030-01-20 23:59:59') |
+--------------------------------------------------------+
|                                                      5 |
+--------------------------------------------------------+

Here’s another example:

SELECT DATEDIFF('2030-01-25 00:00:00', '2030-01-25 23:59:59');

Result:

+--------------------------------------------------------+
| DATEDIFF('2030-01-25 00:00:00', '2030-01-25 23:59:59') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+

In this case, even though it was almost a whole day, it wasn’t quite, and so the result is 0.

Current Date

Here, we pass NOW() as the second argument:

SELECT 
    NOW(),
    DATEDIFF('2030-01-20', NOW());

Result:

+---------------------+-------------------------------+
| NOW()               | DATEDIFF('2030-01-20', NOW()) |
+---------------------+-------------------------------+
| 2021-05-10 10:03:08 |                          3177 |
+---------------------+-------------------------------+

In this case there are 3,177 days between now and the second date.

Invalid Arguments

When passed any invalid arguments, DATEDIFF() returns null:

SELECT DATEDIFF('Homer', 'Simpson');

Result:

+------------------------------+
| DATEDIFF('Homer', 'Simpson') |
+------------------------------+
|                         NULL |
+------------------------------+

Missing Argument

Calling DATEDIFF() the wrong number of arguments, or without passing any arguments, results in an error:

SELECT DATEDIFF();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'

And:

SELECT DATEDIFF('2030-05-21');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'