Cannot convert value '0000-00-00 00:00:00' from column [N] to TIMESTAMP

翔子

error:
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 20 to TIMESTAMP.

solve:
连接数据库的url增加：zeroDateTimeBehavior=convertToNull 或 round

解决方案参考：
[url=http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html]http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
[url=http://bugs.mysql.com/bug.php?id=18308]http://bugs.mysql.com/bug.php?id=18308
Maybe the problem is with connection string. It should include zeroDateTimeBehavior=convertToNull.
con=DriverManager.getConnection("jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull","root","");

翔子

• Datetimes with all-zero components (0000-00-00 ...)— These values can not be
represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being
read from a ResultSet.
Connector/J 3.1 throws an exception by default when these values are encountered as this
is the most correct behavior according to the JDBC and SQL standards. This behavior can be
modified using the zeroDateTimeBehavior configuration property.
The allowable values are:
• exception (the default), which throws an SQLException with an SQLState of S1009.
• convertToNull, which returns NULL instead of the date.
• round, which rounds the date to the nearest closest value which is 0001-01-01.
Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior
via noDatetimeString-Sync=true (the default value is false) so that you can get retrieve
the unaltered all-zero value as a String. It should be noted that this also precludes
using any time zone conversions, therefore the driver will not allow you to enable
noDatetimeString-Sync and useTimezone at the same time.