Post reply

Arithmetic Overflow

  • May 26, 2009 at 9:23 pm

    #134590

    I have data in a column of Decimal datatype(38,20) like this,

    0.00000000000000000000

    10.00000000000000000000

    10.00000000000000000000

    10.00000000000000000000

    0.00000000000000000000

    I need to display this data as,

    0.00

    10.00

    10.00

    10.00

    0.00

    So i do this,

    CAST(IL.[VAT %] AS DECIMAL(2,2)) AS 'VAT'

    But i get the following error,

    Arithmetic overflow error converting numeric to data type numeric.

    How can I return the values as I have stated above?

    Kindest Regards,

  • May 26, 2009 at 9:33 pm

    #1000293

    Try this:

    CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

  • May 26, 2009 at 9:38 pm

    #1000302

    Ok thanks that works but why? Why can't you use 2,2?

    Kindest Regards,

  • May 26, 2009 at 9:51 pm

    #1000308

    Trigger (5/26/2009)

    Ok thanks that works but why? Why can't you use 2,2?

    Because DECIMAL(2,2) represents a decimal number with 2 positions, both to the right of the decimal.

    Please take some time to read about DECIMAL and NUMERIC data types in Books Online (BOL).

  • May 26, 2009 at 10:19 pm

    #1000313

    Lynn Pettis (5/26/2009)

    Try this:

    CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

    hi Lynn,

    CAST(ABC AS DECIMAL(4,2)) AS 'VAT'is better,

    but the orignal datatype is Decimal(38,20),

    if the value like 99999.00000000, then again the Arithmetic overflow error happned.

    so,its may better to use this like

    CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

    ARUN SAS

  • May 26, 2009 at 10:24 pm

    #1000315

    arun.sas (5/26/2009)

    Lynn Pettis (5/26/2009)

    Try this:

    CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

    hi Lynn,

    CAST(ABC AS DECIMAL(4,2)) AS 'VAT'is better,

    but the orignal datatype is Decimal(38,20),

    if the value like 99999.00000000, then again the Arithmetic overflow error happned.

    so,its may better to use this like

    CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

    ARUN SAS

    My response was based on the OP's original post that included the sample data. It is possible that the original definition of DECIMAL(38,20) was simply overkill for the data stored.

  • May 26, 2009 at 10:42 pm

    #1000323

    arun.sas (5/26/2009)

    Lynn Pettis (5/26/2009)

    Try this:

    CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

    but the orignal datatype is Decimal(38,20),

    so,its may better to use this like

    CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

    A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2009 at 10:48 pm

    #1000324

    Paul White (5/26/2009)

    arun.sas (5/26/2009)

    Lynn Pettis (5/26/2009)

    Try this:

    CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

    but the orignal datatype is Decimal(38,20),

    so,its may better to use this like

    CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

    A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:

    Hi,

    Never assume the VAT means “Value Added Tax”.

    Wait till OP’s response.

    ARUN SAS

  • May 27, 2009 at 1:36 am

    #1000380

    arun.sas (5/26/2009)

    Paul White (5/26/2009)

    A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:

    Never assume the VAT means “Value Added Tax”. Wait till OP’s response.

    Msg 50000, Level 25, State 1, Line 1

    General failure in module sense_of_humour.cpl at line 1.

    The connection has been terminated.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 27, 2009 at 2:30 am

    #1000402

    I'm curious about what you mean with "display", Trigger?

    😉

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • May 27, 2009 at 3:25 am

    #1000429

    Old Hand is correct! It's not Value Added Tax.

    Kindest Regards,

  • May 27, 2009 at 3:40 am

    #1000434

    Trigger (5/27/2009)

    Old Hand is correct! It's not Value Added Tax.

    Tough crowd :pinch:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 27, 2009 at 3:47 am

    #1000442

    Paul White (5/27/2009)

    Trigger (5/27/2009)

    Old Hand is correct! It's not Value Added Tax.

    Tough crowd :pinch:

    Real things comes late but latest.

    Fine!!!

    ARUN SAS

  • May 27, 2009 at 4:11 am

    #1000455

    arun.sas (5/27/2009)

    Real things comes late but latest.

    Fine!!!

    You what? :unsure:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 27, 2009 at 8:05 am

    #1000620

    Paul, we need to go have a pint, discuss the flag, and get your head unfuzzled. Your sense of humour is underappreciated here. 😛

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 15 posts - 1 through 15 (of 19 total)

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