Value '0000-00-00' cannot be represented as java.sql.Date

[mmamet]

If you're ever in a situation with Java/MySQL projects where the JDBC driver throws this annoying error: Value '0000-00-00' cannot be represented as java.sql.Date - here is a solution.

The JDBC driver developers for MySQL created an option for how '0000-00-00' dates should be handled (since that technically isn't a valid date from Java's standpoint). Even though this is the default value for NOT NULL date fields in MySQL, they decided to default this option to throw an exception. You can in fact change this behavior to the more sensible option of just returning null. To do this, add "zeroDateTimeBehavior=convertToNull" to the end of the "url" parameter in the server.xml connection pool resource section. Here's what the end product should look like:

  <Resource name="jdbc/mypool" auth="Container" type="javax.sql.DataSource?"
      maxActive="10" maxIdle="2" maxWait="10000"
      username="user" password="password" driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost/mydatabase?user=user&password=password&zeroDateTimeBehavior=convertToNull"/>

Good Luck and Happy Coding. For more useful tips, check out http://www.MatthewMamet.com