Andy Jarrett: MySQL, JDBC Driver and zero dates

Loading Twitter

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

Comments (9) | Print | Send | 7310 Views

If you like what you see on the website and/or this post has helped you out in some way please consider donating to help keep me in beer vodka. The donations are made through Paypal, which accepts almost any credit card or eCheck.

(Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

Posted By djhunx @ 8/18/07 7:39 PM

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

Posted By Patty @ 1/7/08 2:00 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!