在某种程度上，您可以将一个值从一种时间类型转换为另一种时间类型。然而，可能会有数值的改变或信息的丢失。在所有情况下，时间类型之间的转换都受到结果类型有效值范围的限制。例如，虽然 DATE、DATETIME 和 TIMESTAMP 值都可以使用相同的格式集合指定，但这些类型的值范围并不相同。TIMESTAMP 值不能早于 UTC 时间 1970 或晚于 UTC 时间 '2038-01-19 03:14:07'。这意味着像 '1968-01-01' 这样的日期，虽然作为 DATE 或 DATETIME 值是有效的，但作为 TIMESTAMP 值则无效，会被转换为 0。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.

DATE 值的转换：Conversion of DATE values:

转换DATETIME 和 TIMESTAMP 值：Conversion of DATETIME and TIMESTAMP values:

对于将 TIME 值转换为其他时间类型，日期部分使用 CURRENT_DATE() 的值。TIME 被解释为经过的时间（而不是一天中的时间）并加到日期上。这意味着如果时间值超出 '00:00:00' 到 '23:59:59' 的范围，结果的日期部分会与当前日期不同。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'.

假设当前日期是 '2012-01-01'。TIME 值 '12:00:00'、'24:00:00' 和 '-12:00:00'，当转换为 DATETIME 或 TIMESTAMP 值时，分别得到 '2012-01-01 12:00:00'、'2012-01-02 00:00:00' 和 '2011-12-31 12:00:00'。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.

将 TIME 转换为 DATE 类似，但会从结果中丢弃时间部分：'2012-01-01'、'2012-01-02' 和 '2011-12-31'。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.

显式转换可以用来覆盖隐式转换。例如，在比较 DATE 和 DATETIME 值时，DATE 值会被强制转换为 DATETIME 类型，并添加时间部分 '00:00:00'。如果希望在比较时忽略 DATETIME 值的时间部分，可以按照以下方式使用 CAST() 函数：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)

将TIME和DATETIME值转换为数值形式（例如，通过加+0）取决于值是否包含秒的小数部分。TIME(N)或DATETIME(N)在N为 0（或省略）时转换为整数，当N大于 0 时转换为具有N小数位的DECIMAL值：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(N) or DATETIME(N) is converted to integer when 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 |
+---------------------+----------------+--------------------+