最近再做一个评议系统时，遇到这个错误，不知道如何解决 ，在网上百度好多，不过下面的说的挺详细的。

 

下面是我复制粘贴的（嘿嘿）

Featured Database Articles

 

MySQL

 

Jan 17, 2006

Date and Time in MySQL 5 - Page 2

By Ian Gilfillan

The effects of the SQL MODE on date and time behavior

The concept of a TIMESTAMP is rather different in MaxDB. With MySQL AB working towards greater integration between MaxDB and 'ordinary' MySQL, you can now run MySQL in MAXDB mode. In this mode, all TIMESTAMP fields are treated as ordinary DATETIME fields. Let's run the identical commands we ran earlier, but this time in MAXDB mode. We will change to MAXDB mode (and the later modes that follow) just for the particular connection we're running. Quitting and reconnecting will restore all defaults.

mysql> SET SESSION sql_mode=MAXDB;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE dt5(date1 DATE,time1 TIME, timestamp1 TIMESTAMP, timestamp2 TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt5(date1,time1) VALUES ('2006-01-03','15:26:02');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt5\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:26:02
timestamp1: NULL
timestamp2: NULL
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE dt5\G
*************************** 1. row ***************************
       Table: dt5
Create Table: CREATE TABLE "dt5" (
  "date1" date default NULL,
  "time1" time default NULL,
  "timestamp1" datetime default NULL,
  "timestamp2" datetime default NULL
)
1 row in set (0.00 sec)

There is no difference between the first and second TIMESTAMP instances, and both fields are defaulted to NULL, with no on update. You don't need to worry that you will experience problems with previously defined TIMESTAMPs though - since the only real difference now between a DATETIME and a TIMESTAMP is in their default definitions, existing TIMESTAMPS will still behave as expected.

mysql> INSERT INTO dt(date1,time1) VALUES('2006-01-03','17:44:02');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
     date1: 2006-01-03
     time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
2 rows in set (0.00 sec)

The TIMESTAMPs are set to the expected defaults, as before.

Another important change is that before MySQL 5.0.2, day and month values did not have to be legal, merely in the range 0-12, and 1-31. So, dates such as the 31st of February could be safely stored. By default, these are now converted to 0, and throw a warning. 

mysql> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:03');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
     date1: 2006-01-03
     time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
     date1: 0000-00-00
     time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
3 rows in set (0.00 sec)

You can revert to the old behavior with the ALLOW_INVALID_DATES mode.

mysql> SET SESSION sql_mode=ALLOW_INVALID_DATES;
Query OK, 0 rows affected (0.00 sec)
> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:04');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
     date1: 2006-01-03
     time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
     date1: 0000-00-00
     time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
*************************** 4. row ***************************
     date1: 2005-02-31
     time1: 18:04:00
timestamp1: 2006-01-03 18:04:36
timestamp2: 0000-00-00 00:00:00
4 rows in set (0.00 sec)

If allowing invalid dates at all disconcerts you, you can enable one of the strict modes (either STRICT_ALL_TABLES or STRICT_TRANS_TABLES), which will result in MySQL throwing an error if the date is at all invalid.

mysql> SET SESSION sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt(date1,time1) VALUES('2005-02-31','18:06');
ERROR 1292 (22007): Incorrect date value: '2005-02-31' for column 'date1' at row 1

Strict mode still permits zero dates though. To prohibit these, you can use the NO_ZERO_DATE (the entire date cannot be zero) or NO_ZERO_IN_DATE (no part of the date can be zero) modes. A convenient mode to use in this case is TRADITIONAL, which is equivalent to all of the STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO and NO_AUTO_CREATE_USER modes. 

mysql> INSERT INTO dt(date1,time1) VALUES('0000-00-00','18:22');
Query OK, 1 row affected (0.00 sec)
mysql> SET SESSION sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO dt(date1,time1) VALUES('0000-00-00','18:23');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date1' at row 1
mysql> SELECT * FROM dt\G
*************************** 1. row ***************************
     date1: 2006-01-03
     time1: 15:26:02
timestamp1: 2006-01-03 17:08:29
timestamp2: 0000-00-00 00:00:00
*************************** 2. row ***************************
     date1: 2006-01-03
     time1: 17:44:02
timestamp1: 2006-01-03 17:44:25
timestamp2: 0000-00-00 00:00:00
*************************** 3. row ***************************
     date1: 0000-00-00
     time1: 18:03:00
timestamp1: 2006-01-03 18:03:05
timestamp2: 0000-00-00 00:00:00
*************************** 4. row ***************************
     date1: 2005-02-31
     time1: 18:04:00
timestamp1: 2006-01-03 18:04:36
timestamp2: 0000-00-00 00:00:00
*************************** 5. row ***************************
     date1: 0000-00-00
     time1: 18:22:00
timestamp1: 2006-01-03 18:22:03
timestamp2: 0000-00-00 00:00:00

 

来自：http://www.databasejournal.com/features/mysql/article.php/10897_3577131_2/Date-and-Time-in-MySQL-5.htm