Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.4Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 159.8Kb
Man Pages (Zip) - 262.8Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Conversion Between Date and Time Types

11.2.8 Conversion Between Date and Time Types

To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of valid values for the resulting type. For example, although DATE, DATETIME, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. TIMESTAMP values cannot be earlier than 1970 UTC or later than '2038-01-19 03:14:07' UTC. This means that a date such as '1968-01-01', while valid as a DATE or DATETIME value, is not valid as a TIMESTAMP value and is converted to 0.

Conversion of DATE values:

  • Conversion to a DATETIME or TIMESTAMP value adds a time part of '00:00:00' because the DATE value contains no time information.

  • Conversion to a TIME value is not useful; the result is '00:00:00'.

Conversion of DATETIME and TIMESTAMP values:

  • Conversion to a DATE value discards the time part because the DATE type contains no time information.

  • Conversion to a TIME value discards the date part because the TIME type contains no date information.

Conversion of TIME values:

MySQL converts a time value to a date or date-and-time value by parsing the string value of the time as a date or date-and-time. This is unlikely to be useful. For example, '23:12:31' interpreted as a date becomes '2023-12-31'. Time values not valid as dates become '0000-00-00' or NULL.

Explicit conversion can be used to override implicit conversion. For example, in comparison of DATE and DATETIME values, the DATE value is coerced to the DATETIME type by adding a time part of '00:00:00'. To perform the comparison by ignoring the time part of the DATETIME value instead, use the CAST() function in the following way:

date_col = CAST(datetime_col AS DATE)

Conversion of TIME or DATETIME values to numeric form (for example, by adding +0) results in a double-precision value with a microseconds part of .000000:

| CURTIME() | CURTIME()+0   |
| 10:41:36  | 104136.000000 |
mysql> SELECT NOW(), NOW()+0;
| NOW()               | NOW()+0               |
| 2007-11-30 10:41:47 | 20071130104147.000000 |