CODEWITHSUNDEEP
sqlsql-serverinsertdecimalvarchar
Dragan Sekuloski
Dragan Sekuloski

Reputation: 897

SQL server - update VARCHAR value to DECIMAL

Increase the rate by 10 percent of nurse resource in the resource table. enter image description here

I try this code but fails

UPDATE Resourcetbl 
SET  Rate = convert(varchar(255), convert(decimal(10,2), Rate * 1.1))
WHERE ResName = 'nurse';

I get error message Arithmetic overflow error converting varchar to data type numeric.

Rate is VARCHAR(20)

Upvotes: 1

Views: 3110

Answers (4)

Dragan Sekuloski
Dragan Sekuloski

Reputation: 897

unexpecting - this do the job

UPDATE Resourcetbl SET Rate += (Rate*10)/100 WHERE ResName = 'nurse';

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can use CAST

UPDATE Resourcetbl 
SET  Rate = convert(varchar(255),CAST(rate AS DECIMAL(9,2)) * 1.1))
WHERE ResName = 'nurse';

Upvotes: 0

Hüseyin Burak Karadag
Hüseyin Burak Karadag

Reputation: 1888

Because you must convert to decimal your value before arithmetic operation

convert(varchar(255), (convert(decimal(10,2), Rate) * 1.1))

Upvotes: 0

Backs
Backs

Reputation: 24913

Convert Rate to decimal before multiply:

UPDATE Resourcetbl 
SET  Rate = convert(varchar(255), convert(decimal(10,2),Rate) * 1.1)
WHERE ResName = 'nurse';

Upvotes: 1

Related Questions