MySQL, JDBC Driver and zero dates

*** Posted At *** : October 24, 2006 5:45 PM | *** Posted By *** : Andy Jarrett
*** Related Categories ***: MySQL,ColdFusion

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

Error Executing Database Query.<br/>
Cannot 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:

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

*** Comments *** (9) |

*** Send *** |

*** Linking Blogs *** | 4915 *** Views ***

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

[*** Add Comment ***]

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 *** djhunx | 8/18/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!

# *** Posted By *** Patty | 1/7/08 2:00 PM

Helped me out, thanks!