CODEWITHSUNDEEP
mysqldatedatetimecoldfusioncoldfusion-8
BoBFiSh
BoBFiSh

Reputation: 102

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.

Upvotes: 2

Views: 1191

Answers (2)

Mark A Kruger
Mark A Kruger

Reputation: 7193

Just cut to the chase here. Alter your code to:

<!--- create a date object --->
<cfset new_date = CREATEODBCDATETIME(insdate)>
<!--- format for the DB --->
<cfset new_date = dateformat (new_date, 'yyyy-mm-dd') & ' ' & timeformat(new_date,'HH:mm:ss')>

See if that inserts for you. most DBs take a string and implicitly convert to dattime internally.

Upvotes: 1

Alex
Alex

Reputation: 7833

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.)

Upvotes: 1

Related Questions