| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.5-beta pre-release | OS: | Windows (Windows Server 2003) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[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.
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.