Issue
Queries to MySQL may return the error, "java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date" or similar. A sqlstate of S1009 is also seen in the sql exception. This error appears when using the Adobe ColdFusion MySQL (4/5) JDBC driver. It also occurs when using MySQL's Connector/J 3.1 or Connector/J 5.x JDBC drivers.
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 MySQL Connector/J 3.0.x converted dates and datetimes with 0 values to nulls. This behavior changed with MySQL Connector/J 3.1 and later versions. The default behavior now is to throw the exception, "java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date" and sqlstate s1009. The MySQL drivers of ColdFusion 8 and ColdFusion MX 7 are versions of the MySQL Connector/J.
For more information, see the MySQL Connector/J 5.0 release notes and review the bullet labelled "Datetimes."
Solution
ColdFusion
The MySQL Connector/J 3.1 and newer versions support the connectstring property of zeroDateTimeBehavior. The default value of this property is exception and the cause of the error. The other 2 value options are:
convertToNull (returns NULL instead of the date)
round (returns rounded date of 2001-01-01)
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. mirroring the bahavior of the MySQL Connector/J 3.0.
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.
JRun
The fix in Adobe JRun 4 is to create the MySQL datasource as normal in the JMC resources dialog, and then add the same connectstring argument. Alternately, if you have java code that specifies the JDBC URL directly in code, you should again add the connectstring directly to the URL. For example, jdbc:mysql://localhost:3306/databasename?zeroDateTimeBehavior=convertToNull.
If you are using a JRun 4 or ColdFusion datasource of type 'other' and specifying a JDBC URL directly, remember to add the connectstring argument as above. You can do this either in the URL itself or in the connectstring textarea.
