MySQL Bugs: #5086: Year '00' entered displayed as '0000' instead of '2000'

Bug #5086	Year '00' entered displayed as '0000' instead of '2000'
Submitted:	18 Aug 2004 11:20	Modified:	20 Aug 2004 13:40
Reporter:	Bakka Reddy Edla	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.20a	OS:	Windows (wind-2000)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Hi,

I encountered a problem when handling Year type field.
Example queries provided below to reproduce the result.

I have entered data '00' as the input to Year type field, and expecting the output from select query is '2000', but actually i am getting '0000'.

mysql> CREATE TABLE MYSQL_Y2K_TEST(MY_YEAR YEAR);

mysql> DESC MYSQL_Y2K_TEST;

mysql> INSERT INTO MYSQL_Y2K_TEST VALUES (00),(11),(68),(69),(70),(99);

mysql> SELECT * FROM MYSQL_Y2K_TEST;

mysql> select * from mysql_y2k_test;
+---------+
| MY_YEAR |
+---------+
|    0000 |
|    2011 |
|    2068 |
|    2069 |
|    1970 |
|    1999 |
+---------+
6 rows in set (0.00 sec)

How to repeat:
mysql> CREATE TABLE MYSQL_Y2K_TEST(MY_YEAR YEAR);

mysql> DESC MYSQL_Y2K_TEST;

mysql> INSERT INTO MYSQL_Y2K_TEST VALUES (00),(11),(68),(69),(70),(99);

mysql> SELECT * FROM MYSQL_Y2K_TEST;

mysql> select * from mysql_y2k_test;
+---------+
| MY_YEAR |
+---------+
|    0000 |
|    2011 |
|    2068 |
|    2069 |
|    1970 |
|    1999 |
+---------+
6 rows in set (0.00 sec)

Suggested fix:
should display as '2000'

Thank you for the bug report.

From the Manual:

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.

You mentioned in the mail, "Year values in the range 00-69 are converted to 2000-2069." So, the select query should display '00' as '2000' right? But my question is, the query is returning '0000'.

Right. That is the reason I applied the status verified.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

The manual a couple of paragraphs down say:

   * The "zero" values are special, but you can store or refer to them
     explicitly using the values shown in the table.  You can also do
     this using the values `'0'' or `0', which are easier to write.

Thus, if you do

INSERT INTO MYSQL_Y2K_TEST VALUES (0)

you will get 0000.
And 00 is just the same as 0 - it's the same number, no different from 00000000.

You should insert the value as string:

INSERT INTO MYSQL_Y2K_TEST VALUES ('00')

then you will get 2000 inserted.