MySQL Forums
Forum List  »  Docs

Manual 5.0 error about DATE?
Posted by: bruno rossi
Date: September 05, 2007 06:37AM

1 - Not documented behavior: number with a length < than 3 digits

"Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD
or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed
to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these
lengths are interpreted as though padded with leading zeros to the closest length."

The following is a simple test:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@session.sql_mode;
+----------------------------------------------------------------+
| @@session.sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table testdate (mydate DATE);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO testdate VALUES (111111);
Query OK, 1 row affected (0.02 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 2011-11-11 |
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (11111);
Query OK, 1 row affected (0.02 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 2011-11-11 |
| 2001-11-11 |
+------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (1111);
Query OK, 1 row affected (0.01 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 2011-11-11 |
| 2001-11-11 |
| 2000-11-11 |
+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (111);
Query OK, 1 row affected (0.03 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 2011-11-11 |
| 2001-11-11 |
| 2000-11-11 |
| 2000-01-11 |
+------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (11);
ERROR 1292 (22007): Incorrect date value: '11' for column 'mydate' at row 1

If the given number is 11 the inserted date should be 2000-00-11 and it's a valid date if NO_ZERO_IN_DATE sql mode is non enabled. ok, I'm testing it...

mysql> set session sql_mode = CONCAT(@@session.sql_mode, ',NO_ZERO_DATE');
Query OK, 0 rows affected (0.02 sec)

mysql> select @@session.sql_mode;
+-----------------------------------------------------------------------------+
| @@session.sql_mode |
+-----------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2000-00-11');
Query OK, 1 row affected (0.02 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 2011-11-11 |
| 2001-11-11 |
| 2000-11-11 |
| 2000-01-11 |
| 2000-00-11 |
+------------+
5 rows in set (0.00 sec)

mysql> set session sql_mode = REPLACE(@@session.sql_mode,',NO_ZERO_DATE',',NO_ZERO_IN_DATE');
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+--------------------------------------------------------------------------------+
| @@session.sql_mode |
+--------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2000-00-11');
ERROR 1292 (22007): Incorrect date value: '2000-00-11' for column 'mydate' at row 1

ok, these lines seem to confirm my opinion...

my conclusion:
with mysql5.0 if you try to insert a date as a number different than 0 and with a length < than 3 digits the server returns the following:

1) in non strict mode it returns a warning and the value 0000-00-00 will be stored

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

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (11);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.02 sec)

mysql> INSERT INTO testdate VALUES (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.02 sec)

2) in strict mode it returns an error and no value will be stored

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

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (11);
ERROR 1292 (22007): Incorrect date value: '11' for column 'mydate' at row 1

mysql> INSERT INTO testdate VALUES (1);
ERROR 1292 (22007): Incorrect date value: '1' for column 'mydate' at row 1

2 - 0 and '0' isn't the same when you use them to store the date '0000-00-00'

Manual 5.0:
"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."

This isn't true, '0' and 0 are managed in different way! Check the following lines:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> delete from testdate;
Query OK, 3 rows affected (0.03 sec)

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

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES ('0');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.00 sec)

mysql> set session sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.02 sec)

mysql> select @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| STRICT_ALL_TABLES |
+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO testdate VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from testdate;
+------------+
| mydate |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO testdate VALUES ('0');
ERROR 1292 (22007): Incorrect date value: '0' for column 'mydate' at row 1



Edited 2 time(s). Last edit at 09/05/2007 07:12AM by bruno rossi.

Options: ReplyQuote


Subject
Views
Written By
Posted
Manual 5.0 error about DATE?
17212
September 05, 2007 06:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.