Fail to pass datetime value to store procedure

[Guest]

Hi,

I have a store procedure which will receive the date passmeter and I write it as following:

<cfif IsDefined("form.yy")>

     <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#CreateDate(form.yy, form.mm, form.dd)#">

<cfelse>

     <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="null" null="yes">

</cfif>

When I did not input the day, it runs correctly.

However, it returns me the error when I input the day:

Conversion failed when converting datetime from character string.

How can I solve the problem?

[Dan_Bracuk]

Your cf code looks ok.  What does the stored proc look like?

[Guest · Accepted solution]

Finally, I find that I can pass the parameter correctly. The problem is the dynamic sql in store procedure and already solved using CONVERT and CAST function. Thanks.

[BKBK]

Fine. Please kindly mark the question as answered.

[BKBK]

Shouldn't the year be 4 digits instead of 2, that is, yyyy? In any case, you should validate the input values. (Update: The following example is a simple validation routine. You could make yours as sophisticated as you want.)

<!--- Validate form field values yy, mm and dd before passing them to stored procedure --->

<cfif IsDefined("form.yy") and isNumeric(form.yy & form.mm & form.dd) and isDate(form.mm & "/" & form.dd & "/" & form.yy)>

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#CreateDate(form.yy, form.mm, form.dd)#">

<cfelse>

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" null="yes">

</cfif>