Arithmetic overflow error converting nvarchar to data type numeric.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Arithmetic overflow error converting nvarchar...

Arithmetic overflow error converting nvarchar to data type numeric.

Rate Topic

Display Mode

Topic Options

Author

Message

duro654321

Posted Saturday, October 13, 2012 10:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20

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!

Post #1372436

Gopi S

Posted Saturday, October 13, 2012 12:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:47 AM
Points: 151, Visits: 277

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

Post #1372445

duro654321

Posted Saturday, October 13, 2012 12:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20

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))

Post #1372448

Gopi S

Posted Saturday, October 13, 2012 12:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:47 AM
Points: 151, Visits: 277

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

Post #1372452

duro654321

Posted Saturday, October 13, 2012 12:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20

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

Post #1372455

L' Eomot Inversé

Posted Saturday, October 13, 2012 1:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 7,104, Visits: 7,168

Gopi S (10/13/2012)

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1372460

duro654321

Posted Sunday, October 14, 2012 1:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20

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?

Post #1372484

duro654321

Posted Sunday, October 14, 2012 2:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20

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 ?

Post #1372489

Gopi S

Posted Sunday, October 14, 2012 5:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:47 AM
Points: 151, Visits: 277

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

Post #1372497

Sean Lange

Posted Monday, October 15, 2012 7:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1372735

« Prev Topic | Next Topic »

Permissions