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.


Solution:

  1. Select the MySQL database connection in the objects tree,
  2. Select the Properties tab for the connection,
  3. In the list of categories select Driver Properties,
  4. In the list of properties locate zeroDateTimeBehavior,
  5. 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,
  6. Enter one of the valid choices (typically convertToNull) in the value field,
  7. Click Apply and then connect the database.