Search code examples
mysqldatedatetimecoldfusioncoldfusion-8

Data truncation: Incorrect datetime value - coldfusion

I'm working on a submission form for events that once completed, goes to a processing page and updates a database.

I have fourteen fields, several are required one of which is the date and time of the event.

During the processing of the form I join the two form entries, date and time before attempting the insert into the database but an error occurs.

Data truncation: Incorrect datetime value

Here is the code elements that are failing:

<cfset insdate = form["date"] & form["time"]>
<cfset new_date = #CREATEODBCDATETIME(insdate)#>

<cfif len(trim("#institle#"))>
        <cfquery name="modify">
            INSERT INTO table
            SET
            title = <cfqueryparam                      
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#institle#">,
            dateTime = <cfqueryparam 
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#new_date#">,
            location = <cfqueryparam 
                     cfsqltype="CF_SQL_LONGVARCHAR" 
                     value="#inslocation#">,
            category = <cfqueryparam 
                     cfsqltype="CF_SQL_SMALLINT" 
                     value="#inscategory#">,
            type = <cfqueryparam 
                     cfsqltype="CF_SQL_TINYINT" 
                     value="#instype#">                                   
         </cfquery>
</cfif>

I've trimmed the code above to make it shorter and easier to read. If anyone has any ideas what I'm doing wrong, that would be great.

I'm running Coldfusion 8, a mySQL database and the database accepts datetime on the field in question, in a yyyy-mm-dd hh:mm:ss format.

Cheers.

Solution

  • ColdFusion can handle string representations of several datetime formats using cfSqlType="CF_SQL_TIMESTAMP", as already suggested. There is no need to create a date(time) object for the query at all. Just make sure that isDate(yourDateTimeString) returns true for the string (because that's what CF_SQL_TIMESTAMP will assume) and be aware of differences in the locale. (ddmmyyyy and mmddyyyy are two obnoxious formats that will be mixed up by ColdFusion, I guarantee it.)