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()
).