Date time exception in coldfusion query in cfc and mySQL

Question

I'm moving from an MS Access backend to mySQL. This used to work but now doesn't and I can't figure the problem.

<cfargument required="false" name="expiry" type="any" default="" />


        <cfquery datasource='#arguments.dsn#'>      
            INSERT INTO users(expiry)
            VALUES (<cfqueryparam value="#arguments.expiry#" cfsqltype="CF_SQL_TIMESTAMP"/>)
        </cfquery>  

The database field is set to datetime and default NULL

The argument is populated from a form field which is either empty, or a javascript validated date. It chokes on empty formfield.

Answer 1

Before you mess with the DSN settings, I would also try changing your <cfqueryparam> to the following:

<cfqueryparam value="#arguments.expiry#" cfsqltype="CF_SQL_TIMESTAMP" null="#len(arguments.expiry) eq 0#" />

This will pass a true null in the event that the argument value is an empty string.

Answer 2

CF's implementation of the JDBC driver for MySQL doesn't handle NULL dates very well.

You need to add a config flag to your DSN connection string settings (under advanced) in the CF admin

&zeroDateTimeBehavior=convertToNull

Should set you right.

Rob