Bug #7297 Date decoding trouble
Submitted: 14 Dec 2004 20:35 Modified: 30 Dec 2004 20:50
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:Various OS:Various
Assigned to: Dmitry Lenev

[14 Dec 2004 20:35] Carsten Pedersen
Description:
According to the documentation, http://dev.mysql.com/doc/mysql/en/DATETIME.html

  You can specify DATETIME, DATE, and TIMESTAMP values using any of 
  a common set of formats:
  ...
  * As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' 
    syntax is allowed here, too. For example, '98-12-31', '98.12.31', 
    '98/12/31', and '98@12@31' are equivalent.
  ...
  Dates containing two-digit year values are ambiguous because the century 
  is unknown. MySQL interprets two-digit year values using the following rules:
  * Year values in the range 00-69 are converted to 2000-2069.
  * Year values in the range 70-99 are converted to 1970-1999. 

However, various versions of MySQL give varying results when doing this:

How to repeat:
CREATE TABLE datetest (d DATE DEFAULT NULL);
INSERT INTO datetest VALUES ('12:00:00');
SELECT * FROM datetest;

The SELECT statement returns: "2012-00-00" (correct) on 
* Version 3.23.58/Linux
* Version 4.0.8/WinNT

but returns: "0012-00-00" (incorrect) on
* Version 4.0.14 / Win98
* Version 4.1.7 / Linux 

However, on 4.1.7 / Linux, inserting "12:01:01" will get
the date accepted as "2012-01-01".
[15 Dec 2004 12:50] Carsten Pedersen
Another user reports that on MySQL version 4.1.7-0 on RedHat 9 for x86, the behaviour is as documented.
[16 Dec 2004 13:33] Dmitry Lenev
ChangeSet
  1.2013 04/12/16 16:31:50 dlenev@mysql.com +3 -0
  Fix for bug #7297 "Two digit year should be interpreted correctly
  even with zero month and day" aka "Date decoding trouble"

  Two digit year should be interpreted correctly as year in 20th or 21st
  century even with zero month and day. Only exception should be zero date
  '00-00-00' or '00-00-00 00:00:00'.
[30 Dec 2004 20:50] Dmitry Lenev
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet
  1.2013 04/12/16 16:31:50 dlenev@mysql.com +3 -0
  Fix for bug #7297 "Two digit year should be interpreted correctly
  even with zero month and day" aka "Date decoding trouble"