MySQL Error, “java.sql.SQLException: Value ‘0000-00-00’
Published by exdone
Posted on April 12, 2009
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.
- An error has occurred, which probably means the feed is down. Try again later.