MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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 of DATETIME and
TIMESTAMP values:
Conversion to a DATE value
takes fractional seconds into account and rounds the time
part. For example, '1999-12-31
23:59:59.499' becomes
'1999-12-31', whereas
'1999-12-31 23:59:59.500' becomes
'2000-01-01'.
Conversion to a TIME value
discards the date part because the
TIME type contains no date
information.
For conversion of TIME values to
other temporal types, the value of
CURRENT_DATE() is used for the
date part. The TIME is
interpreted as elapsed time (not time of day) and added to the
date. This means that the date part of the result differs from
the current date if the time value is outside the range from
'00:00:00' to '23:59:59'.
Suppose that the current date is
'2012-01-01'.
TIME values of
'12:00:00', '24:00:00',
and '-12:00:00', when converted to
DATETIME or
TIMESTAMP values, result in
'2012-01-01 12:00:00', '2012-01-02
00:00:00', and '2011-12-31
12:00:00', respectively.
Conversion of TIME to
DATE is similar but discards the
time part from the result: '2012-01-01',
'2012-01-02', and
'2011-12-31', respectively.
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_colAS DATE)
Conversion of TIME and
DATETIME values to numeric form
(for example, by adding +0) depends on
whether the value contains a fractional seconds part.
TIME(
or
N)DATETIME(
is converted to integer when N)N is 0
(or omitted) and to a DECIMAL value with
N decimal digits when
N is greater than 0:
mysql>SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;+-----------+-------------+--------------+ | CURTIME() | CURTIME()+0 | CURTIME(3)+0 | +-----------+-------------+--------------+ | 09:28:00 | 92800 | 92800.887 | +-----------+-------------+--------------+ mysql>SELECT NOW(), NOW()+0, NOW(3)+0;+---------------------+----------------+--------------------+ | NOW() | NOW()+0 | NOW(3)+0 | +---------------------+----------------+--------------------+ | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 | +---------------------+----------------+--------------------+