Handling Datetime Values 0000-00-00 00:00:00 in Jdbc

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:

In hibernate.cfg.xml:

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

In hibernate.properties:

hibernate.connection.zeroDateTimeBehavior=convertToNull

But I had to put it in my mysql-ds.xml file for JBOSS as:

<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>

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.

Try adding 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 null instead:

select
    ...
    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:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

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

In MySql '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:

SELECT
  CASE WHEN `date`!='0000-00-00' THEN `date` END new_date
FROM
  yourtable

or you can add to your datasource connection string this:

zeroDateTimeBehavior=convertToNull

and dates as '0000-00-00' will be automatically converted to NULL.

---

Related Topics