SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Arithmetic overflow error converting nvarchar to d
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

duro
Starting Member

5 Posts

Posted - 10/13/2012 :  13:56:06  Show Profile  Reply with Quote
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!

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 10/13/2012 :  14:02:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you using nvarchar for the price column? Use money instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

duro
Starting Member

5 Posts

Posted - 10/13/2012 :  14:13:40  Show Profile  Reply with Quote
thank you for your reply almight sql goddess,
when i go into the design view and try and change it to money, then i try and save it, it says "saving changes is not permitted"
any ideas?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 10/13/2012 :  15:48:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't use the design view to make schema changes, can't help you there. I use ALTER TABLE. It'll be something like this, but be sure to check out ALTER TABLE in Books Online and check out the examples.

ALTER TABLE t1 ALTER COLUMN Price money NOT NULL -- or whatever

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

duro
Starting Member

5 Posts

Posted - 10/14/2012 :  04:51:39  Show Profile  Reply with Quote
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 ?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:14:47  Show Profile  Reply with Quote
Couple of things:

a) As Tara suggested, the problem (in ordering and in updating) is happening because you have a nvarchar column for the price. That is not a good choice because prices, if I am not mistaken, should always contain numeric values. If you are allowed to change the data type, change it as Tara suggested. If you are on a production server, be very careful - there may be other code that depends on the column being of type nvarchar

When you try to change it in the design view, it is not letting you change it because of a setting. To change this setting, in SSMS, go to Tools-> Options -> Designers -> Table and Database Designers and uncheck the "Prevent saving changes that require table-recreation". HOWEVER, that means, when you make that change, SQL Server is really recreating the table for you. It will do that very gracefully - but still if your table has a lot of records, that can be a scary thing to do, especially on a production server.

b) As to your question about why the prices are not being ordered as you expected - again, this is because the column is of character type. In character ordering, each character starting from the left is looked at. So comparing 115.50 and 36.00, it compares the 1 and 3 and decides that 1 is less than 3, so 115.50 will come before 36.00. Change your ordering to this:
select * from table order by CAST(price AS FLOAT) asc
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000