Bug #10532 Accepts '0' for 'YYYY' format in DATE datatype.
Submitted: 11 May 2005 7:57 Modified: 24 Aug 2005 12:52
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.5-beta pre-release OS:Microsoft Windows (Windows Server 2003)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[11 May 2005 7:57] Disha
Description:
Create a table say 'd1' using DATE datatype. Set the sql_mode as 'traditional'. Insert the value in table 'd1' with 0 in 'yyyy' format of DATE 'yyyy-mm-dd' , it accepts. If we insert the value 0 for 'mm' or 'dd' it fails and displays an error message.

How to repeat:
1. delimiter //
2. create database test//
3. use test
4. set @@sql_mode='traditional'//
5. create table d1 (f1 date)//
6. insert into d1 values ('0-1-1')//
7. select * from d1//
8. The execution of step (7) displays the result as,
+------------+
| f1         |
+------------+
| 0000-01-01 |
+------------+

Expected result : Execution of step (6) should fail with an error message same 
       as for 'mm' and 'dd'.

Actual Result : Accepts '0' for 'yyyy' format of DATE datatype.
[24 Aug 2005 12:52] Georg Richter
Why do you expect an error here? 0000-01-01 is a valid date, 0000-00-01 isn't. 

mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)

mysql> insert into d1 values ('0-0-0');
Query OK, 1 row affected (0.00 sec)

mysql> set sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,TRADITIONAL";
Query OK, 0 rows affected (0.00 sec)

mysql> insert into d1 values ('0-12-24');
Query OK, 1 row affected (0.00 sec)

mysql> insert into d1 values ('0-1-0');
ERROR 1292 (22007): Incorrect date value: '0-1-0' for column 'f1' at row 1
[13 May 2007 17:18] sandeep singh
hi dear can this possible to insert data in date field in table in dd-mm-yyyy
because there's a problem we have software that is off line we are attaching that off line software to online my sql in that we have old format of date is dd-mm-yyyy
please help me i am really helpless.
thanks.