How TO_DAYS() Works in MariaDB

In MariaDB, TO_DAYS() is a built-in date and time function that returns the number of days from the start of the standard calendar (0000-00-00), based on a given date.

You pass the date to the function when you call it.

The TO_DAYS() function is the converse of the FROM_DAYS() function.

Syntax

The syntax goes like this:

TO_DAYS(date)

Where date is the date for which to return the number of days since 0000-00-00.

Example

Here’s an example:

SELECT TO_DAYS('2028-12-31');

Result:

+-----------------------+
| TO_DAYS('2028-12-31') |
+-----------------------+
|                741077 |
+-----------------------+

Based on Today’s Date

Here’s an example that uses NOW() to return the number of days between 0000-00-00 and today:

SELECT
    NOW(),
    TO_DAYS(NOW());

Result:

+---------------------+----------------+
| NOW()               | TO_DAYS(NOW()) |
+---------------------+----------------+
| 2021-05-23 09:56:45 |         738298 |
+---------------------+----------------+

Invalid Arguments

When passed an invalid argument, TO_DAYS() returns NULL with a warning:

SELECT TO_DAYS('Oops!');

Result:

+------------------+
| TO_DAYS('Oops!') |
+------------------+
|             NULL |
+------------------+
1 row in set, 1 warning (0.000 sec)

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1292 | Incorrect datetime value: 'Oops!' |
+---------+------+-----------------------------------+

Missing Argument

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

SELECT TO_DAYS();

Result:

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

And another example:

SELECT TO_DAYS( '2028-12-31', '2029-12-31' );

Result:

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

Pre-Gregorian Calendar

The TO_DAYS() function is not designed for use with dates before the advent of the Gregorian calendar (which was introduced in October 1582). Results will not be reliable since it doesn’t account for the lost days when the calendar changed from the Julian calendar.