Skip to main content
C
Cainlord
Participating Frequently
February 10, 2010
Question

Upgrade to Cold Fusion 8, Date time stamps in procedures not working

  • February 10, 2010
  • 1 reply
  • 961 views

We recently upgraded to Cold Fusion 8, and some of the code we have is now not working correctly.

We generate the date time stamp, and are passing this value to a stored procedure.

Here is the parameter line:

<cfprocparam type="IN" cfsqltype=CF_SQL_TIMESTAMP VALUE=#startDateTime# DBVARNAME=@start>

Where startDateTime when outputted prior to this procedure being ran contains:

{ts '2010-02-09 00:00:00'}

This works on Cold Fusion 7.  However, on Cold Fusion 8, I am getting the following error:

Syntax error during implicit conversion of VARCHAR value '2010-02-09 00:00:00.000000000' to a SMALLDATETIME field

On top of the error in general, from what I can tell the value being passes is having the .000000000 portion added on that is not there in the variable.

Any ideas?

    This topic has been closed for replies.

    1 reply

    -
    -__cfSearching__-
    Inspiring
    February 10, 2010

    How exactly are you using the value inside the procedure? It sounds like you are using as a string, instead of a datetime object. Which might account for the syntax error.

    <cfprocparam type="IN" cfsqltype=CF_SQL_TIMESTAMP

    VALUE=#startDateTime# DBVARNAME=@start>

    I thought dbVarName was deprecated?

    C
    CainlordAuthor
    Participating Frequently
    February 10, 2010

    The value is being passed in be used as a smalldatetime type.

    This is legacy code, which is where the DBVARNAME portion is coming from.  the order however of this is correct as far as what the procedure is expecting so no issues there.

    Just not sure why now that this same logic works fine on 7 is not working on 8?

    ilssac
    ilssac
    Inspiring
    February 10, 2010

    yeah sorry about that.  I have been working primarily in PERL now so its been a bit coming back to cold fusion and this one is a stumper right out of the gate as to why it appears the type has changed.


    The change that is probably tripping you up is a change in the database *driver* between ColdFusion and your database.

    Before it was just providing a date time value that contained year, month, day, hour, minute and seconds.

    Now it is providing a date time value that containes year, month, day, hour, minute, seconds and milliseconds.

    And your database datatype of smalldate does not allow the milliseconds.

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