Converting Between Dates and Days
Problem
You have a date but want a value in days, or vice versa.
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.
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. ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.