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.