Posts Tagged zeroDateTimeBehavior

JDBC and the “zero” date

A few weeks ago, I was in the process of writing a little script in Java to extract data from a MySQL database. Because this is what we can call a micro project, I decided to simply use JDBC to talk to the database.

But when I tried to run my script, I got the following exception:

java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Date
	at com.mysql.jdbc.SQLError.createSQLException(
	at com.mysql.jdbc.ResultSet.getDateFromString(
	at com.mysql.jdbc.ResultSet.getDate(
	at com.mysql.jdbc.ResultSet.getDate(
	at com.mysql.jdbc.ResultSet.getDate(

After looking at the database, it seems that this exception has been thrown because the fields of type datetime contain the value ‘0000-00-00 00:00:00’. Don’t ask me why I have this kind of value in the database! Obviously, we would prefer to have a NULL value instead of a “zero” date.

There are two solutions to fix this problem:

  1. Replace all the “zero” dates by NULL using the following SQL query:

    UPDATE table SET datefield = NULL WHERE datefield = '0000-00-00 00:00:00';
  2. Add the parameter zeroDateTimeBehavior=convertToNull to the connection url which will automatically convert the “zero” date to NULL. Please find below an example of connection url with this parameter:


Personally, I choose the second solution as I didn’t want to change the original data of the database.

, , , ,

No Comments