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
Do/Can Abstract Classes Replace Interfaces
Does Java Se 8 Have Pairs or Tuples
How to Serialize Only the Id of a Child with Jackson
What's an Example of Duck Typing in Java
Javac Source and Target Options
Find the Most Popular Element in Int[] Array
How to Make a Countdown Timer in Java
Things Possible in Intellij That Aren't Possible in Eclipse
Environment Variable with Maven
How Does the Jvm Ensure That System.Identityhashcode() Will Never Change
Generating Rsa Keys in Pkcs#1 Format in Java
How to Add a Timeout Value When Using Java's Runtime.Exec()
How to Stop a Thread Created by Implementing Runnable Interface
Block()/Blockfirst()/Blocklast() Are Blocking Error When Calling Bodytomono After Exchange()