Arithmetic overflow error converting nvarchar to data type numeric.

  • hello

    when i run the following sql query:

    "update table set price = price * 1.1 "

    i get the following error :

    "Msg 8115, Level 16, State 8, Line 1

    Arithmetic overflow error converting nvarchar to data type numeric.

    The statement has been terminated."

    the table is set to nvarchar, and i am just trying to make the prices go up 10%

    any ideas?

    thanks!

  • Hi,

    In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.

    say 10.1, 10.2...... where the precision value is exceeding 2.

    To resolve this try

    update table set price = price * cast(1.1 as numeric(10,4))

    Note: If your price still has more that 10 precision value then change it accordingly.

    Thanks

    Gopi

  • the value of the price field is:

    .05

    .25

    .6

    1

    1.25

    150.7

    250

    265.75

    etc

    so if there are no items price that are greater than $1000 then i would use the following: ?

    update field set price = price * cast(1.1 as numeric(1000,7))

  • no. '1000' is precision 4. so you can have numeric(4,0). ie 4 decimal values in the left of decimal point and not numeric(1000,7)

    Ref: http://msdn.microsoft.com/en-us/library/ms187746.aspx

    You can understand what is numeric data type and what is precision and scale number

  • i ran "update table set price = price * cast(1.1 as numeric(10,4))" and that worked pretty well

    thanks! πŸ™‚

  • Gopi S (10/13/2012)


    Hi,

    In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.

    say 10.1, 10.2...... where the precision value is exceeding 2.

    To resolve this try

    update table set price = price * cast(1.1 as numeric(10,4))

    Note: If your price still has more that 10 precision value then change it accordingly.

    Thanks

    Gopi

    Yes, it's the implicit conversion that's going wrong because the default type for 1.2 is numeric(2,1). But there's another problem: if you try to increase a price like 10.37 by 10%, just multiplying by 1.1 doesn't work because it will deliver 11.387 which may not be a valid currency amount (eg if the currency is euro the smallest value is .01, so .007 is invalid) and you have to decide what rounding you want to do in this case - perhaps rounding to nearest valid number (and what ever you want to do to round something ending in .xy5) or perhaps rounding down or perhaps some other rule.

    So I think I would go for something like

    update table

    set price = convert(nvarchar(36), cast(cast(price as numeric(35,2))*1.1 as numeric(35,2)))

    (the inner cast should solve the error; the outer cast does simple default rounding, so replace it with something that does whatever sort or rounding you need if the default isn't what you want)

    Of course if you have a currency unit that splits into 1000 parts instead of 100, you need to use scale 3 instead of scale 2, and so on for other splits. If you will sometimes want to multiply by something more complicated than 1.1 (for example by 1.0625, for a 6ΒΌ% increase) you need to reduce the precision from 35 to avoid the possibility of the multiply operation doing some rounding instead of whatever rounding you have chosen to do - pushing it down to 32 isn't going to put much of a constraint on your prices, unless you have prices of 1000000000000000000000000000000.00 currency units or more. In fact you could just look and see what your nvarchar price column is declared as: if it's nvarchar(N) you should use precision N-1, unless whoever specified it was being sloppy.

    Tom

  • thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    that worked, it cleaned up the data from 5.50000000 to 5.5

    but now when i run "select top 10 * from table order by price "

    it shows me

    x 9.9

    x 9.9

    instead of 10 +

    ??

    any ideas?

  • when i run

    select * from table order by price asc

    i get...

    115.50

    36.00

    4.95

    4.95

    4.95

    47.30

    49.50

    49.50

    53.90

    when i should get

    4.95

    4.95

    4.95

    36.00

    47.30

    49.50

    49.50

    53.90

    115.50

    what do i need to change in the sql query ?

  • You are trying to order the varchar column and not numeric though the value looks numeric.

    Try

    --Query 1

    select

    price

    from table

    order by CAST(price as numeric(10,4)) asc

    --Query 2

    select price from (

    select CAST(price as numeric(10,4))

    price

    from table) T

    order by price asc

    Again this is just hint. Your cast function should have appropriate precision and scale based on previous post

  • Or even better, change the column type so you don't have to continually fight the fact that you are storing numbers in a character column.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks for this post - it helped me solve a similar problem.

    But, wouldn't it be more clear to use:

    update table set price = cast(price as numeric(10,4)) * 1.1

Viewing 11 posts - 1 through 10 (of 10 total)

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