Blog

  • 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

Tags: mysql , jdbc

View All PostsLeave a Comment
comments powered by Disqus

Mobile Applications

Video Content

Bigdata & NoSQL

SaaS Applications

Miscellaneous

Archives


Alexa Certified Site Stats for www.oodlestechnologies.com