Converting Between Dates and Days

5.12.1 Problem

You have a date but want a value in days, or vice versa.

5.12.2 Solution

DATE values can be converted to and from days with TO_DAYS( ) and FROM_DAYS( ). Date-and-time values also can be converted to days if you're willing to suffer loss of the time part.

5.12.3 Discussion

TO_DAYS( ) converts a date to the corresponding number of days, and FROM_DAYS( ) does the opposite:

mysql> SELECT d,
 -> TO_DAYS(d) AS 'DATE to days',
 -> FROM_DAYS(TO_DAYS(d)) AS 'DATE to days to DATE'
 -> FROM date_val;
+------------+--------------+----------------------+
| d | DATE to days | DATE to days to DATE |
+------------+--------------+----------------------+
| 1864-02-28 | 680870 | 1864-02-28 |
| 1900-01-15 | 693975 | 1900-01-15 |
| 1987-03-05 | 725800 | 1987-03-05 |
| 1999-12-31 | 730484 | 1999-12-31 |
| 2000-06-04 | 730640 | 2000-06-04 |
+------------+--------------+----------------------+

When using TO_DAYS( ), it's probably best to stick to the advice of the MySQL Reference Manual and avoid DATE values that occur before the beginning of the Gregorian calendar (1582). Changes in the lengths of calendar years and months prior to that date make it difficult to speak meaningfully of what the value of "day 0" might be. This differs from TIME_TO_SEC( ), where the correspondence between a TIME value and the resulting seconds value is obvious and has a meaningful reference point of 0 seconds.

If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time. This provides another means of extracting dates from DATETIME and TIMESTAMP values:

mysql> SELECT dt,
 -> TO_DAYS(dt) AS 'date part in days',
 -> FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE'
 -> FROM datetime_val;
+---------------------+-------------------+-------------------+
| dt | date part in days | date part as DATE |
+---------------------+-------------------+-------------------+
| 1970-01-01 00:00:00 | 719528 | 1970-01-01 |
| 1987-03-05 12:30:15 | 725800 | 1987-03-05 |
| 1999-12-31 09:00:00 | 730484 | 1999-12-31 |
| 2000-06-04 15:45:30 | 730640 | 2000-06-04 |
+---------------------+-------------------+-------------------+
mysql> SELECT ts,
 -> TO_DAYS(ts) AS 'date part in days',
 -> FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE'
 -> FROM timestamp_val;
+----------------+-------------------+-------------------+
| ts | date part in days | date part as DATE |
+----------------+-------------------+-------------------+
| 19700101000000 | 719528 | 1970-01-01 |
| 19870305123015 | 725800 | 1987-03-05 |
| 19991231090000 | 730484 | 1999-12-31 |
| 20000604154530 | 730640 | 2000-06-04 |
+----------------+-------------------+-------------------+