Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata