Converting Varchar Value to Integer/Decimal Value in SQL Server
Solution 1
Table structure...very basic:
create table tabla(ID int, Stuff varchar (50));
insert into tabla values(1, '32.43');
insert into tabla values(2, '43.33');
insert into tabla values(3, '23.22');
Query:
SELECT SUM(cast(Stuff as decimal(4,2))) as result FROM tabla
Or, try this:
SELECT SUM(cast(isnull(Stuff,0) as decimal(12,2))) as result FROM tabla
Working on SQLServer 2008
Solution 2
You are getting arithmetic overflow. this means you are trying to make a conversion impossible to be made. This error is thrown when you try to make a conversion and the destiny data type is not enough to convert the origin data. For example:
If you try to convert 100.52 to decimal(4,2) you will get this error. The number 100.52 requires 5 positions and 2 of them are decimal.
Try to change the decimal precision to something like 16,2 or higher. Try with few records first then use it to all your select.
Solution 3
The reason could be that the summation exceeded the required number of digits - 4. If you increase the size of the decimal to decimal(10,2)
, it should work
SELECT SUM(convert(decimal(10,2), Stuff)) as result FROM table
OR
SELECT SUM(CAST(Stuff AS decimal(6,2))) as result FROM table
Philips Tel
Updated on November 16, 2020Comments
-
Philips Tel over 3 years
How to convert the value of records in a table with data type
varchar
tointeger
ordecimal
so I can get the sum of the value. I useSQL Server 2005
.Say that I have table structure like this:
ID int Stuff varchar
and the records
ID = 1, Stuff = 32.43 ID = 2, Stuff = 43.33 ID = 3, Stuff = 23.22
Now I want to get the total of column Stuff:
SELECT SUM(convert(decimal(4,2), Stuff)) as result FROM table
But it doesn't work. Please need help!