Pritesh     Feb,08 2017

java.sql.SQLException: Value ''0000-00-00'' can not be represented as java.sql.Date

Got strange issue today while working with mySql and ColdFusion. This might be old issue but we never face issue since we normally work with MS Sql and I think this one is second/third project which use mysql as database. Error says java.sql.SQLException: value '.........with long garbage string and special characters......' can not be represented as java.sql.Date and this is due to mysql store 0000-00-00 stored as Null value of date. 

Simple solution is in ColdFusion administrator, open your datasource in edit mode. Click on "Show Advance Settings" button and enter "zeroDateTimeBehavior=convertToNull" (without quote) in connection string textarea and save by clicking "Submit" button. That's it and you are now good to go. More information you can find here.