MySQL, JDBC Driver and zero dates

I am in the middle of moving a lot of my datasources over to the updated Connector/J (JDBC)

One error I have seen a lot in my development enviorment is

view plain print about

1Error Executing Database Query.<br/>
2Cannot convert value '0000-00-00 00:00:00' from column xxxx to TIMESTAMP.

This is caused by Java as it cannot handle zero dates ('0000-00-00 00:00:00'), it trys to do anything it can with them but convert them to the original date :o). To get round this you can set a parameter in the JDBC URL called zeroDateTimeBehavior to convertToNull.

So in your Data Sources page you should have something similar to:

view plain print about

1jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull

Posted: 24-Oct-2006

Permalink: here

Comments

Hi, I'm working on a small program that uses a mysql database with some of the date fields default to '0000-00-00 00:00:00'. I've searched the internet for a solution and found yours to be the one I need. Thanks...

#1 djhunx
18/Aug/07 7:39 PM

This tip works from CF8 Administrator as well:

Data -> Services -> Datasources -> MySQL(4/5)
Advanced Settings
Add: zeroDateTimeBehavior-convertToNull
to the Connection String Field

Thanks for the tip! Solved my issues too!
Good Work!

#2 Patty
07/Jan/08 2:00 PM

Helped me out, thanks!

#3 Joe
05/Feb/08 11:01 AM

Thanks for the tip! Solved my problem !!

#4 Adi
24/Feb/08 12:35 AM

Thanks Andy and Patty!

added...
zeroDateTimeBehavior=convertToNull
...to the connection string field in the CF8 admin for the MySQL4/5 DSN

#5 shaunw
24/Mar/08 7:02 AM

Just what I needed. This made my week, thanks.

#6 Kirk
14/Apr/08 10:24 AM

Thanks Andy. You certainely made my day

#7 Madhav
24/Jul/08 7:11 AM

Thanks Andy, I had this error connecting a Mysql DB using Eclipse SQL Explorer Plugin With Myslq J Connector 5.1.
Solved!

#8 hmart
19/Oct/08 6:11 PM

You are the best!!!

#9 Uday
05/May/09 8:37 PM

Thanks a lot Andy:
Got my issue with Pentaho solved like a breeze:
Under connection > edit > database name > I just added the string: ?zeroDateTimeBehavior=convertToNull
That's it !

#10 DBMaster
19/Oct/10 2:23 PM

Thanks a lot andy for your valuable suggestion.

#11 prakash
21/Sep/11 10:28 AM

Nice one dude

#12 Stuart
08/Feb/12 6:23 PM

Andy Jarrett

MySQL, JDBC Driver and zero dates

Comments