Scenario:
Query grabs user data from MySQL 5 database using the built in MySQL 4/5 driver in ColdFusion 8 on a Windows 2003 Server.
Problem:
Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP error returned on execution.
Analysis:
After a quick search on Google, it appears that the default behaviour for the Connector J MySQL driver is to not allow zero date values. A specific value must be included in the connect string to overide this value.
Resolution:
Include the zeroDateTimeBehavior=convertToNull value in the connect string. In ColdFusion the instructions are:
- Go to Data Sources in the ColdFusion Administrator
- Select the MySQL 4/5 datasource and click the Show Advanced Settings button
- In the Connect String box append the following: zeroDateTimeBehavior=convertToNull
Note: if there is already a connect string add an ampersand to the end of the existing string before adding the new value. For example if the connect string was set to:
useUnicode=true&characterEncoding=iso-8859-1
The new string would look like:
useUnicode=true&characterEncoding=iso-8859-1&zeroDateTimeBehavior=convertToNull
