Issue:
Queries to MySQL may return the error, “java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp” or similar. This error appears when using the Adobe ColdFusion MySQL 5 JDBC driver.
Reason:
This error occurs when the MySQL Date or DateTime columns have a value of all zeros (for example, ‘0000-00-00 00:00:00’). The default behavior now is to throw the exception, “java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp”.
Solution:
The default value of this property is exception and the cause of the error. Setting the connectstring value of zeroDateTimeBehavior=convertToNull will eliminate this exception and cause null values to return from the database. In this case, zero value date and datetime will appear as empty strings in ColdFusion output.
The specific steps to fix this in ColdFusion are to create a MySQL datasource in the ColdFusion administrator as usual. However, you must also click the Advanced Settings button and enter the connectstring value of zeroDateTimeBehavior=convertToNull.
Note: Do not enter apostrophes or quotes. Also, this is case-sensitive and must be entered just as shown.
