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