How to handle DATETIME values with zero Timestamp in JDBC
Posted By : Yasir Zuberi | 30-Jun-2015
While quering into MySQL database, you might receive java.sql.SQLException like below
Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp. Stacktrace follows:
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
Why it occured
The above exception caused by storing zero dates ('0000-00-00 00:00:00') in MySQL and trying to convert them into date objects in Java.
The important point here is that, in MySql considers this '0000-00-00' to be a valid date, but it can't be repesented as java.sql.Date.
MySQL JDBC driver will throw java.sql.SQLException because Java does not understand dates in this format '0000-00-00'.
Steps to solve it-
Here are two simple and short solution which could resolve this error for you.
1. You could possibly change your database schema, to allow NULL values.
UPDATE table SET datefield = NULL WHERE datefield = '0000-00-00 00:00:00';
2. In your datasource configuration, you can edit JDBC URL by setting a parameter called 'zeroDateTimeBehavior' to 'convertToNull'.
jdbc:mysql://localhost:3306/yourMySqlDatabase?zeroDateTimeBehavior=convertToNull
Hope this helps you out there.
Thanks,
Yasir
About Author
Yasir Zuberi
Yasir is Lead Developer. He is a bright Java and Grails developer and have worked on development of various SaaS applications using Grails framework.