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.

The cause of this output exception was that: coldfusion.runtime.locale.CFLocaleBase$InvalidDateTimeException: on is an invalid date or time string.. — Saul, Jun 22 '10 at 15:24

score 6 · Accepted Answer · answered Jun 22 '10 at 15:42

6

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.

answered Jun 22 '10 at 15:42

Adam Tuttle

19,505
17
80
113

This sorted me out Adam, ty, problem was mySQL not put a zero length string as a null, Access has no such qualms !! – Saul Jun 22 '10 at 16:30

score 1 · Answer 2 · edited Jun 22 '10 at 15:41

1

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

edited Jun 22 '10 at 15:41

Adam Tuttle

19,505
17
80
113

answered Jun 22 '10 at 15:38

Rob Dudley

110
1
1
8

Date time exception in coldfusion query in cfc and mySQL

2 Answers2