Resolving MySQL time conversion exceptions

Resolving MySQL time conversion exceptions

To connect to the MySQL database, an exception is thrown by default when the timestamp type with an operation value of 0 cannot be correctly handled:
Java. SQL. SQLException: Cannot convert value '2017-00-00 00:00:00 'from column 7 to TIMESTAMP.

This question has the detailed instruction in the official document, detailed see below link: http://bugs.mysql.com/bug.php? Id = 19274
We recommend that you configure zeroDateTimeBehavior = convertToNull In the JDBC connection string as follows:

Jdbc: mysql: // localhost: 3306/mydbname? ZeroDateTimeBehavior = convertToNull

The zeroDateTimeBehavior attribute of the JDBC connection string has the following three attribute values:
1> exception: default value, that is, the exception of SQL state [S1009]. Cannot convert value... is thrown;
2> convertToNull: converts a date to a NULL value;
3> round: replace it with the latest date, that is, 0001-01-01;

Program avoidance

The triggering of such exceptions is also related to the timestamp assignment operation. If logical judgment can be made during the design phase and the record writing phase,
Avoid writing values like '2014-00-00 00:00:00 ', and avoid the error of Cannot convert value '2014-00-00 00:00:00' from column N to TIMESTAMP.
1> when inserting, the value is determined to be null when inserting 0, or change to now () when inserting 0 ();

2> query data: SELECT id, name, if (date = '2017-00-00 00:00:00 ', NULL, date) AS date FROM 'date ', this will convert the value from 0000-00-00 00:00:00 to null.
3> set SQL _model:
Mysql_query ("set SQL _mode = 'no _ zero_date '"); # If 0 is inserted in the temestamp column, a warning is generated, but the data is still inserted successfully.
Mysql_query ("set SQL _mode = 'strict _ all_tables, no_zero_date '"); # If 0 is inserted, the following ERROR is reported: ERROR 1292 (22007): Incorrect datetime value: '0' for column 'date' at row 1

References:
Http://bugs.mysql.com/bug.php? Id = 19274

This article permanently updates the link address: