Skip navigation
Phinehas1234
Phinehas1234
Currently Being Moderated

Fail to pass datetime value to store procedure

Apr 25, 2012 6:27 PM

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?

562 Views   4 Replies   Latest reply: BKBK, Apr 27, 2012 12:45 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Apr 26, 2012 2:59 AM   in reply to Phinehas1234
    Report

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

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    2. BKBK,
    Apr 26, 2012 3:19 AM   in reply to Phinehas1234
    Report

    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>

     
    |
    Mark as:
  • Phinehas1234
    Currently Being Moderated
    3. Phinehas1234,
    Apr 26, 2012 7:03 PM   in reply to Dan Bracuk
    Report

    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.

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    4. BKBK,
    Apr 27, 2012 12:45 AM   in reply to Phinehas1234
    Report

    Fine. Please kindly mark the question as answered.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points