LogikDevelopment

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(SQLError.java:910)
	at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:2032)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1956)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1924)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1972)
	...

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:

   jdbc:mysql://localhost:3306/dbname?zeroDateTimeBehavior=convertToNull
   

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