Skip to content

TO_DAYS()

Function Description

TO_DAYS() is used to calculate the number of days difference between a given date and the start date of the calendar (January 1, 0000). If date is NULL, NULL is returned.

Note

0000-00-00 and 0000-01-01 The date itself is considered invalid. MatrixOne year query should start from 0001. Query 0000-00-00 and 0000-01-01, TO_DAYS() returns the error message:

mysql> SELECT TO_DAYS('0000-00-00');
ERROR 20301 (HY000): invalid input: invalid datatime value 0000-00-00
mysql> SELECT TO_DAYS('0000-01-01');
ERROR 20301 (HY000): invalid input: invalid datatime value 0000-01-01

For double-digit years in a date, such as querying SELECT TO_DAYS('08-10-07');, MatrixOne automatically completes year 08 to 0008, unlike MySQL. For more information, see Double-digit Year in Date.

Function Syntax

> TO_DAYS(date)

Example

-- The query will return an integer indicating the difference in the number of days between the date '2023-07-12' and the date of the calendar start.
mysql> SELECT TO_DAYS('2023-07-12');
+--------------------------+
| to_days(2023-07-12) |
+--------------------------+
| 739078 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');
+-----------------------------------------------------------+
| to_days(2008-10-07) | to_days(08-10-07) |
+-----------------------------------------------------------+
| 733687 | 3202 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)