Accessing a table in the Data tab or in the SQL Commander that contain a DATETIME column sometimes report the following error and highlights the column in red:
Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
This error is generated by the MySQL JDBC driver as a result of having defined the default value for the DATETIME column to 0000-00-00 00:00:00. The default behavior when this happen is that the driver fails.
- Select the MySQL database connection in the objects tree,
- Select the Properties tab for the connection,
- In the list of categories select Driver Properties,
- In the list of properties locate zeroDateTimeBehavior,
The following is an excerpt from the Connector/J documentation that describes the zeroDateTimeBehaviorproperty:
What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to
represent invalid dates)? Valid values are exception, round and convertToNull,
- Enter one of the valid choices (typically convertToNull) in the value field,
- Click Apply and then connect the database.