MySQL Bugs: #550: DATE value YYYY-00-00 not allowed

Bug #550	DATE value YYYY-00-00 not allowed
Submitted:	30 May 2003 21:43	Modified:	31 May 2003 11:08
Reporter:	Scott Anderson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23.56	OS:	MacOS (Mac OS X 10.2.6)
Assigned to:		CPU Architecture:	Any

Description:
According to the documentation for MySQL, in a DATE value, the month and day may be set to zero:

"The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999."

and

"MySQL allows you to store some wrong date values into DATE and DATETIME columns (like 2000-02-31 or 2000-02-00). The idea is that it's not the SQL server job to validate date. If MySQL can store a date and retrieve exactly the same date, then MySQL will store the date. If the date is totally wrong (outside the server's ability to store it), then the special date value 0000-00-00 will be stored in the column."

My goal is to indicate an unknown month (YYYY-00-00) or day (YYYY-MM-00). This "feature" is explicitly described in the O'Reilly book "Managing and Using MySQL" on p. 318. However, when I try to set such a date, the month and day are always set to "-01".

How to repeat:
UPDATE document SET DocumentDate = '1889-00-00' WHERE DocumentID = 547

The month and day are always set to 01:

1889-01-01

Suggested fix:
I inquired about this on the mailing list, but there were no responses to indicate that I'm doing something wrong. So either this is a bug, or you removed this "feature".

You do seem to indicate that this is a bug that you intend to correct, as the second quote above is preceded by "The following problems are known and will be fixed in due time:". If, in fact, you have already "fixed" this, the documentation is incorrect.

Thanks,

Scott R. Anderson
srca@mindspring.com

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I couldn't repeat it. 

mysql> create table d (d date);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into d values (curdate());
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from d;
+------------+
| d          |
+------------+
| 2003-05-31 |
+------------+
1 row in set (0.00 sec)
 
mysql> update d set d = '2004-07-00';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from d;
+------------+
| d          |
+------------+
| 2004-07-00 |
+------------+
1 row in set (0.00 sec)
 
mysql> update d set d = '2005-00-00';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from d;
+------------+
| d          |
+------------+
| 2005-00-00 |
+------------+
1 row in set (0.00 sec)

I tested it with both MyISAM and InnoDB tables as and also with 3.23, 4.0 and 4.1

Thanks for looking into this. I followed your steps exactly. My results were:

     2004-07-01

(instead of 2004-07-00) and

     2005-01-01

(instead of 2005-00-00).

"I tested it with both MyISAM and InnoDB tables as and also with 3.23,
4.0 and 4.1"

I was using MyISAM tables. Did you try this on Mac OS X?

One thought. I used a precompiled version; is there possibly an option to enforce dates that might have been turned on by the person who compiled it? I didn't see one in the documentation, but maybe it's hidden.

Seems like we shall have to test it on OS X.

If we can't repeat it, then it has been fixed meanwhile in our source tree.

We were not able to repeat it on OS X either.
It is either some specific setting in your setup or a bug that was meanwhile fixed.