Converting the datatype of a computed column

Question

Converting the datatype of a computed column

crazy_new

SSCarpal Tunnel

Points: 4804
More actions
March 10, 2015 at 8:26 am

#298408

Hi All,
I have a computed column that I want to cast as decimal.
The two columns it calculates from are both varchar.
Why can I cast the column as INT, but when I try to cast as decimal, I get the following error?
Arithmetic overflow error converting varchar to data type numeric.
Can anyone please advise as to what this error means and why I get it only when I want to cast to decimal.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Would need to see the specific cast and data values to be sure, but most likely the whole decimal places weren't large enough to hold the character value.

For example, you specified "decimal(6, 2)" but the character string was "12345.67".

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".

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 2

Hey man,

I figured it out. The one column has a data type of small int, so I just had to cast it to int.

Thanks anyway

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 3

For some reason it only works when I have a where clause, as soon as I remove the where clause, I get the error again

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 4

I am still getting that same error.

How can it get the data when I have a where clause just filtering the data, but as soon as I remove the where clause it gives me and error.

I don't understand how filtering on the address has anything to do with the datatype of the computed column?????:crazy:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The where clause is filtering out some row that has a value that throws that error. With the where clause, the row is not in the resultset and the conversion error never occurs.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 6

Hi Gila, well that makes sense hehe.

Is there a way to find out which row is causing the problem?

I have made the length of the decimal (25,2) and I still get the error.

The longest value in this column has a length of 13.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Filters on a select to limit down rows until you have the smallest subset that does throw the error. Then look at the computed column definition and see what it would work out to for those rows.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 8

Hey Gila,

I have sorted it out. My focus was on the wrong part of the case statement which was char data.

Thanks man.