Post reply

Cast and Convert

  • July 20, 2007 at 11:26 am

    #125118

    The ReplyAVMLotsize field is an 'INT' datatype, while the sa_lotsize is a decimal(14,0)

    I need to update the AVMLotSize with the SA_lotsize.

    However, I can run the updates below for a 100 records, but then I get the following error message

    Msg 8115, Level 16, State 2, Line 3

    Arithmetic overflow error converting expression to data type int.

    The statement has been terminated.

    I tried both cast and convert...

    update

    details

    set

    AVMLotSize = CONVERT(int, sa_LotSize)

    from

    details

    where

    sa_property_id = sa_property_id

    and

    avmlotsize is null

     

    or

    update

    details

    set

    AVMLotSize = cast(sa_LotSize as int)

    from

    details

    where

    sa_property_id = sa_property_id

    and

    avmlotsize is null

     

  • July 20, 2007 at 11:38 am

    #721233

    what happens when you

    select cast(sa_LotSize as int)

    from details

    where

    sa_property_id = sa_property_id /* why is this here?*/

    and

    avmlotsize is null

  • July 20, 2007 at 11:39 am

    #721234

    Figured it out..

    Thanks

    Susan

     

  • July 20, 2007 at 12:06 pm

    #721238

    glad you figured it out, but it would be beneficial to others that read this post to know what the issue was, as there be others with a similar issue.

  • July 20, 2007 at 12:24 pm

    #721247

    most likely the data doesn't convert because decimal(14,0) is bigger than an int.

    begin

    declare @dec decimal(14,0)

    set @dec = 99999999999999

    select cast(@dec as int)

    end

    when it hits the row that has a bigger number it fails.

  • July 20, 2007 at 5:11 pm

    #721303

    Susan,

    Please post what you figured out... thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply