handling DATETIME values 0000-00-00 00:00:00 in JDBC
I stumbled across this attempting to solve the same issue. The installation I am working with uses JBOSS and Hibernate, so I had to do this a different way. For the basic case, you should be able to add
zeroDateTimeBehavior=convertToNull to your connection URI as per this configuration properties page.
I found other suggestions across the land referring to putting that parameter in your hibernate config:
But I had to put it in my mysql-ds.xml file for JBOSS as:
Hope this helps someone. :)
Mysql datetime becomes 0000-00-00 00:00:00 in mysql version 5.6
As the server side prepared statements are disabled by default from Connector/J 5.0.5 on, there is a fair chance that combined with the introduction of fractional second support in MySQL 5.6.4 they cause your problem.
useServerPrepStmts=false to your connection string (which should also ensure, that
System.out.println really prints the SQL which is executed and not just an approximation).
MySQL Incorrect datetime value: '0000-00-00 00:00:00'
My suggestion if it is the case that the table is empty or not very very big is to export the create statements as a .sql file, rewrite them as you wish. Also do the same if you have any existing data, i.e. export insert statements (I recommend doing this in a separate file as the create statements). Finally, drop the table and execute first create statement and then inserts.
You can use for that either
mysqldump command, included in your MySQL installation or you can also install MySQL Workbench, which is a free graphical tool that includes also this option in a very customisable way without having to look for specific command options.
How to process 0000-00-00 date in jdbc MySQL query
I like @a_horse_with_no_name's answer, however if you don't have control over the connection, you could change the query to return a
case when my_date_col = '0000-00-00' then null else my_date_col end as my_date_col,
or the slightly more terse, but mysql-only, option:
if(my_date_col = '0000-00-00', null, my_date_col) as my_date_col
Also, caution is advised changing the entire application's JDBC behaviour as you may break code that relies on such dates being returned - perhaps they use
rs.getString(i) instead. You would have to regression test all other queries to be sure.
0000-00-00 00:00:00' can not be represented as java.sql.Timestamp error
You can use this JDBC URL directly in your data source configuration:
How to handle date having value 0000-00-00 00:00:00 in java
Set the MySQL JDBC driver option
zeroDateTimeBehavior=convertToNull. You can set it in the connection string. Then you won't get these dates, you will get nulls instead.
Value '0000-00-00' can not be represented as java.sql.Date
'0000-00-00' is considered a valid date, but it can't be repesented as java.sql.Date.
You could use a query that returns NULL in case date is
'0000-00-00', or the actual value otherwise:
CASE WHEN `date`!='0000-00-00' THEN `date` END new_date
or you can add to your datasource connection string this:
and dates as
'0000-00-00' will be automatically converted to NULL.