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
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
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.