I have a column of data that contains 9 digits of numeric values, but the column is defined as a varchar. I need to CAST the field to a numeric value so I can divide the sum of the column by 100. e.g.
select CAST(field1 as numeric(9,2)) / 100 from table;
I get the following error when running the query: Arithmetic overflow error converting varchar to data type numeric.
If I perform a double CAST from varchar -> int -> numeric, the CAST works. e.g.
select CAST(CAST(field1 as int) as numeric(9,2)) / 100 from table;
Is there a reason why the single CAST from varchar -> numeric results in a SQL error, but the double CAST works?
Best Answer
If your field contains 9 digits, it could be a max of
999,999,999
. My original thought was that your cast should beCAST (NUMERIC 11, 2)
edit
To be on the safe side, with 9 character length, you could have numbers ranging from
999,999,999
to0.1234567
. This means you need 9 digits before the decimal point and 7 after (total 16). Therefore your cast should beCAST (NUMERIC (16,7)
select CAST(field1 as numeric(16,7) from table;