Cannot convert value ‘0000-00-00 00:00:00? from column X to TIMESTAMP
I'm currently moving a site from MySQL 4 and ColdFusion 7 on IIS 6 to MySQL 5 and ColdFusion 9 on IIS 7. One of the first problems that I noticed was the following error on one of my database driven templates:
Cannot convert value ‘0000-00-00 00:00:00? from column X to TIMESTAMP.
After a little investigation, I found that the table being queried had numerous invalid timestamps consisting of all zeros. This did not seem to cause problems with MySQL 4 and ColdFusion 7 but does cause the above error with ColdFusion 9 and MySQL 5. There are two options that can be used to resolve this issue. The obvious solution is to update the invalid timestamps in the table to valid time date stamps. The other option is to enter the following in the datasource settings in the ColdFusion Administrator in the connection string dialog:
noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull
Comments
Glad that the post was helpful. I'm not aware of a way to make this change on the MySql side, but that does not mean that it is not possible.
Thanks for this tip.. Same migration as yours, same errors in my queries.. This fixed the issue instantly! ps: Can this change be made on the MySQL administrator? (I'm using MySQL WorkBench) just curious..