content_image/806726.jpg content_image/806727.jpg
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_col  AS 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( 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: 

## 11.2.8 2-Digit Years in Dates 

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0; 

<img src='content_image/806726.jpg'>

mysql> SELECT NOW(), NOW()+0, NOW(3)+0; 

<img src='content_image/806727.jpg'>

Date values with 2-digit years are ambiguous because the century is unknown. Such values must be interpreted into 4-digit form because MySQL stores years internally using 4 digits. 

For DATETIME , DATE , and TIMESTAMP  types, MySQL interprets dates specified with ambiguous year values using these rules: 

•Year values in the range 00-69  become 2000-2069 . 

•Year values in the range 70-99  become 1970-1999 . 

For YEAR , the rules are the same, with this exception: A numeric 00  inserted into YEAR  results in 0000 rather than 2000 . To specify zero for YEAR  and have it be interpreted as 2000 , specify it as a string ’0’  or ’00’ . 

Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the values you require, you must provide unambiguous input containing 4-digit year values. 

ORDER BY  properly sorts YEAR  values that have 2-digit years. 

Some functions like MIN()  and MAX()  convert a YEAR  to a number. This means that a value with a 2-digit year does not work properly with these functions. The fix in this case is to convert the YEAR  to 4-digit year format. 

## 11.3 String Data Types 

