October 29, 2015 at 4:35 pm
Below doesn't work for varchar column, Any ideas?
select sum(cast(Enter_your_field_name as int)) from Table_name
THanks
October 29, 2015 at 4:57 pm
Worked fine in my test. Maybe I'm using a different version. What does this return on your instance?
CREATE TABLE #Temp (
AVarcharColumn VARCHAR(20)
)
INSERT INTO #Temp
(AVarcharColumn)
VALUES ('0')
GO
SELECT SUM(CAST(AVarcharColumn AS INT)) FROM #Temp
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2015 at 5:09 pm
I am running on 2008 SQL Server enterprise. Below is what i am getting
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
October 29, 2015 at 5:14 pm
Try this:
select sum(cast(cast(replace(Enter_your_field_name, ',', '') as decimal(28, 6) as int)) from Table_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2015 at 5:16 pm
Admingod (10/29/2015)
I am running on 2008 SQL Server enterprise. Below is what i am gettingMsg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
You've got values in the string column that are too large to convert to int. Try CAST .. AS BIGINT, or use a DECIMAL that's large enough to hold the values once converted to a numeric type.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2015 at 6:28 pm
Quick question - Why are you storing numeric values in a string column?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply