Troubleshooting
Problem
Trying to update certain records within a MySQL DB using the JDBC connector leading to errors like: --- CTGDIS495I handleException , update, java.sql.SQLException: Value ' ... 0000-00-00 00:00:00 ... ' can not be represented as java.sql.Timestamp ---
Cause
By default the MySQL Java Connector is throwing this exception while processing empty/null values for datetime fields.
Resolving The Problem
Set configuration property "zeroDateTimeBehavior=convertToNull" for MySQL.
This can be done directly within the JDBC URL like:
---
jdbc:mysql://hostname:port/dbname?zeroDateTimeBehavior=convertToNull
---
See MySQL Manual:
---
Connector/J x.x 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.
---
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21971231