4 Replies Latest reply on Nov 13, 2008 11:30 AM by Dan Bracuk

    cf procs

    craig_mac Level 1
      Hi i am tring to pass a date to ms sql via cfproc i have add in param as

      <cfprocparam type="In" cfsqltype="CF_SQL_DateTime" dbvarname="InvoiceDate" value="#DateFormat(CreateODBCDate(form.txtInvoiceDate), "dd-mm-yyyy")#" null="No">

      but i am getting a varchar to datetime exeption

      any ideas?
        • 1. Re: cf procs
          JR "Bob" Dobbs-qSBHQ2 Level 3
          Try this
          <cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" value="#DateFormat(form.txtInvoiceDate, 'dd-mm-yyyy')#" null="No">

          Note:
          The MS SQL server datatype DATETIME maps to CF_SQL_TIMESTAMP.
          Removed CreateODBCDate function call
          Corrected nested quotes in value attribute contents
          The dbvarname attribute is no longer supported
          You may also try a date format string 'yyyy-mm-dd' or 'mm-dd-yyyy' depending on the configuration of your SQL server

          http://msdn.microsoft.com/en-us/library/ms189491.aspx
          • 2. Re: cf procs
            Level 7
            > <cfprocparam type="In" cfsqltype="CF_SQL_DateTime" dbvarname="InvoiceDate"
            > value="#DateFormat(CreateODBCDate(form.txtInvoiceDate), "dd-mm-yyyy")#"
            > null="No">
            >
            > but i am getting a varchar to datetime exeption

            If it's after a date, why are you passing it a string? dateFormat() is for
            converting a date into a string, which you'd only generally do when
            outputting the date for a human.

            Pass the DB a date.

            PS: the dbvarname attribute of <cfprocparam> is not supported on most
            recent versions of CF. This is probably not relevant to your situation,
            but it's worth bearing in mind.

            --
            Adam
            • 3. Re: cf procs
              craig_mac Level 1
              ok thanks,

              but i need to pass the format from a form field

              dd-mm-yyyy
              so whats the best way to convert this to a date?
              • 4. Re: cf procs
                Dan Bracuk Level 5
                createdate()