"Data truncation: Incorrect datetime value" Error Message

[simonbingham]

Hello,

I'm running Coldfusion 8 with a MySQL database. The s10Date column is of type "datetime".

What would cause the following error to occur when I'm entering data into the database?

---

Data truncation: Incorrect datetime value: '{ts '2008-09-03 17:16:01'}' for column 's10Date' at row 1

The error occurred in C:\ColdFusion8\wwwroot\Websites\questnbs.info\components\DataMgr\DataMgr.cfc: line 1602
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\components\DataMgr\DataMgr.cfc: line 1170
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\components\DataMgr\DataMgr.cfc: line 1633
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\parsed\quest_c_registrations.processregistrationform.cfm: line 37
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\parsed\quest_c_registrations.processregistrationform.cfm: line 5
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\parsed\quest_c_registrations.processregistrationform.cfm: line 1
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\fusebox5\Application.cfc: line 228
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\fusebox5\Application.cfc: line 218
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\fusebox5\fusebox5.cfm: line 57
Called from C:\ColdFusion8\wwwroot\Websites\questnbs.info\index.cfm: line 12

1600 : <cfquery name="qQuery" datasource="#variables.datasource#" username="#variables.username#" password="#variables.password#"><cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1"><cfif IsSimpleValue(aSQL )><cfset temp = aSQL>#Trim(PreserveSingleQuotes(temp))#<cfelseif IsStruct(aSQL )><cfset aSQL = queryparam(argumentCollection=aSQL )><cfswitch expression="#aSQL.cfsqltype#"><cfcase value="CF_SQL_BIT"><cfif aSQL .value>1<cfelse>0</cfif></cfcase><cfcase value="CF_SQL_DATE">#CreateODBCDateTime(aSQL.value)#</cfcase><cfdefaultcase><cfif ListFindNoCase(variables.dectypes,aSQL .cfsqltype)>#Val(aSQL.value)#<cfelse><cfqueryparam value="#aSQL .value#" cfsqltype="#aSQL.cfsqltype#" maxlength="#aSQL .maxlength#" scale="#aSQL.scale#" null="#aSQL .null#" list="#aSQL.list#" separator="#aSQL .separator#"></cfif></cfdefaultcase></cfswitch></cfif> </cfloop></cfquery>
1601 : <cfelse>
1602 : <cfquery name="qQuery" datasource="#variables.datasource#"><cfloop index="i" from="1" to="#ArrayLen(aSQL)#" step="1"><cfif IsSimpleValue(aSQL)><cfset temp = aSQL >#Trim(PreserveSingleQuotes(temp))#<cfelseif IsStruct(aSQL)><cfset aSQL = queryparam(argumentCollection=aSQL)><cfswitch expression="#aSQL .cfsqltype#"><cfcase value="CF_SQL_BIT"><cfif aSQL.value>1<cfelse>0</cfif></cfcase><cfcase value="CF_SQL_DATE">#CreateODBCDateTime(aSQL .value)#</cfcase><cfdefaultcase><cfif ListFindNoCase(variables.dectypes,aSQL.cfsqltype)>#Val(aSQL .value)#<cfelse><cfqueryparam value="#aSQL.value#" cfsqltype="#aSQL .cfsqltype#" maxlength="#aSQL.maxlength#" scale="#aSQL .scale#" null="#aSQL.null#" list="#aSQL .list#" separator="#aSQL.separator#"></cfif></cfdefaultcase></cfswitch></cfif> </cfloop></cfquery>
1603 : </cfif>
1604 :

---

Thank you in advance for your assistance.

Simon

[Dan_Bracuk]

Are the quotes around the curly brackets here:
'{ts '2008-09-03 17:16:01'}'
yours or the error message's?

If they're yours get rid of them. Otherwise, does the error message show you the sql that results from all that code starting at line 1600 of your cfc?

[simonbingham]

Dan,

Thank you for your response. I'm not sure how I did it, but I have managed the fix the problem. I switched my computer on this morning and the problem had actually fixed itself! I like it when that happens. ;o)

Simon

[cmschofield]

Just a note, I had the same problem and resolved it by making sure my cfsqltype in cfqueryparam is cf_sql_timestamp instead of cf_sql_datetime (which is not a valid value).

Also, just a note that when working with a datetime value in cfqueryparam, using cf_sql_date for cfsqltype will truncate the time to 00:00:00.

[Karzons]

Thanks, where can I find more information about this?