Skip to main content
A
Anonymous
April 26, 2012
Answered

Fail to pass datetime value to store procedure

  • April 26, 2012
  • 2 replies
  • 1185 views

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?

    This topic has been closed for replies.
    Correct answer

    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.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 26, 2012

    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>

    D
    Dan_Bracuk
    Inspiring
    April 26, 2012

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

    A
    Anonymous
    Correct answer
    April 27, 2012

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 27, 2012

    Fine. Please kindly mark the question as answered.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices