Date time exception in coldfusion query in cfc and mySQL

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 chok开发者_如何学Pythones on empty formfield.

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.

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