sql date time insert error

[Guest]

Hi i am having trouble entering a date in to a sql stored procedure.

first i convert the date to the users current datetime using

<cfscript>
userTZ="#qGetClubDetails.LocalTime12#"; // or whatever tz ID you need
tz=createObject("component","timeZone");
userDateTime=tz.castFromServer(today,userTZ);
</cfscript>

then i need to insert this into a stored procedure using

<cfstoredproc procedure="xsp_InsertSmsRecords" datasource="#application.ds#">
<cfprocparam type="In" cfsqltype="CF_SQL_DATETIME" dbvarname="UsersDate" value="#CREATEODBCDATETIME(userDateTime)#" null="No">
</cfstoredproc>

but i am getting a error of
Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to datetime.

any ideas what i need to do?

[Dan_Bracuk]

Is userDateTime a string or datetime? If it's a string, use the parsedatetime function to convert it.

[Guest]

ok i have tried that with no luck, i did a cfdump on userDateTime and i got

{ts '2008-01-21 05:16:04'}

any ideas what i need to do to insert this to sql server

[Dan_Bracuk]

quote:

---

Originally posted by: craiglaw98
ok i have tried that with no luck, i did a cfdump on userDateTime and i got

{ts '2008-01-21 05:16:04'}

any ideas what i need to do to insert this to sql server

---

Looks ok to me. To troubleshoot this, try using cfquery for your insert instead of a stored procedure.