Post reply

error when trying to change scale of decimal data type

  • November 12, 2009 at 2:21 pm

    #137271

    hi guys, i have a column with datatype decimal (11,2), i am trying to change it to decimal (11,6)

    i am using this script:

    ALTER TABLE TABLEtest ALTER COLUMN COLUMNNAME DECIMAL(11,6)

    but i keep getting this error:

    Arithmetic overflow error converting numeric to data type numeric.

    can you help me? I do have existing values.

  • November 12, 2009 at 2:24 pm

    #1078609

    Try changing it to DECIMAL(15,6) instead of DECIMAL(11,6).

  • November 12, 2009 at 2:38 pm

    #1078617

    thanks for you answer:

    yes it works, but it has to be (11,6), i also tried doing this :

    update table

    set column = round(column, 6, 0)

    ALTER TABLE table ALTER COLUMN columnanme DECIMAL(11,6)

    but still same error

  • November 12, 2009 at 2:59 pm

    #1078623

    Here is the problem, you are going from this:

    123456789.01

    to this:

    12345.678901

    Unless you are absolutely sure that no values in this column is greater than 99999.99, then what you may need to do is create a new column with new DECIMAL definition, copy the data to the new column, drop the old column, and then rename the new column to the old column name using sp_rename.

  • November 12, 2009 at 3:35 pm

    #1078633

    yes i do have two values greater than 99999.99, what can i do?

  • November 12, 2009 at 3:51 pm

    #1078638

    No idea. It is your data, you have decide if the data can be modified or not. If not, then you will have to go with a larger defined value for the column.

  • November 12, 2009 at 4:22 pm

    #1078644

    DBA-640728 (11/12/2009)

    thanks for you answer:

    yes it works, but it has to be (11,6),

    conflicts with

    DBA-640728 (11/12/2009)

    yes i do have two values greater than 99999.99

    DECIMAL(11,6) will result in a format of XXXXX.XXXXXX (5 digits before the decimal point, 6 after). See BOL for details.

    You basically have four choices:

    1) Expand DEC(11,6) to DEC(15,6)

    2) Get a business rule for values greater than 99999.99 to fit into DEC(11,6)

    3) Leave it as it is right now, DEC(11,2) or

    4) Use something in between, e.g. DEC(11,4)

    The first two option were already mentioned by Lynn. The other two are just to complete the list (take it, change it, leave it or find a compromise). 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 13, 2009 at 6:37 am

    #1078879

    Thank you both!

Viewing 8 posts - 1 through 7 (of 7 total)

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