In MySQL, you can use the TO_DAYS() function to find out how many days have passed since day 0 for a particular date. For example, you can pass today’s date to this function, and it will return how many days it’s been since day 0.

This article contains examples to demonstrate.

Syntax

The syntax goes like this:

TO_DAYS(date)

Where date is the date to use in the calculation.

Example

Here’s an example to demonstrate.

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

Result:

+-----------------------+
| TO_DAYS('1999-12-31') |
+-----------------------+
|                730484 |
+-----------------------+

Note that the MySQL documentation advises that this function is not intended for use with values that precede the advent of the Gregorian calendar (1582). This is because it does not take into account the days that were lost when the calendar was changed.

Example 2 – Current Date

Here’s an example using the current date.

SELECT 
    CURDATE(),
    TO_DAYS(CURDATE());

Result:

+------------+--------------------+
| CURDATE()  | TO_DAYS(CURDATE()) |
+------------+--------------------+
| 2018-06-26 |             737236 |
+------------+--------------------+

First, I use the CURDATE() function to return the current date, then I pass that function to the TO_DAYS() function to return the number of days its been since day 0.

TO_DAYS() vs FROM_DAYS()

The FROM_DAYS() function is the opposite of TO_DAYS(), which, given a date date, returns the day number. Here’s an example to demonstrate the relationship between these two functions:

SELECT 
    CURDATE(),
    TO_DAYS(CURDATE()),
    FROM_DAYS(TO_DAYS(CURDATE()));

Result:

+------------+--------------------+-------------------------------+
| CURDATE()  | TO_DAYS(CURDATE()) | FROM_DAYS(TO_DAYS(CURDATE())) |
+------------+--------------------+-------------------------------+
| 2018-06-26 |             737236 | 2018-06-26                    |
+------------+--------------------+-------------------------------+

So in this example I use TO_DAYS() to return the number of days from the current date. I then use FROM_DAYS() to return the date from that value (which, as expected, resolves back to the original value of CURDATE()).