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

Request for Proposal

Recaptcha is required.

Sending message..