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
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'.
Hope this helps you out there.