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

view plain print about

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

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

view plain print about

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

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

Comments (9) |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 6105 Views

TweetBacks

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

[Add Comment] [Subscribe to 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...

# 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!