MySQL Bugs: #7297: Date decoding trouble

Bug #7297	Date decoding trouble
Submitted:	14 Dec 2004 21:35	Modified:	30 Dec 2004 21:50
Reporter:	Carsten Pedersen
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	Various	OS:	Various
Assigned to:	Dmitri Lenev	Target Version:

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".

Another user reports that on MySQL version 4.1.7-0 on RedHat 9 for x86, the behaviour is
as documented.

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'.

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"