Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00
sql, sql-server-2008, t-sql
Solution
This generates an Arithmetic Overflow because it is trying to implicitly cast the `Val` column to a NUMERIC(3,2), which naturally will overflow on a 2-digit value like 10.
It's using NUMERIC(3,2) as the target type and size because that is the smallest numeric that `9.00` appears to fit into.
The solution, of course, is to use explict CASTing instead of doing it implicitly
Problem
Below is a subset of the kind of table structure and data i'm working with. ``` CREATE TABLE #Test ( Val varchar(5) ,Type varchar(5) ) INSERT #Test VALUES ('Yes','Text') INSERT #Test VALUES ('10','Int') INSERT #Test VALUES ('10.00','Float') INSERT #Test VALUES ('9.00','Float') INSERT #Test VALUES ('9','Int') ``` I want to write a query that will let me know if the column 'Val' is <= 9.00 (must be of numeric data type). I did this by doing the following: ``` SELECT * FROM ( SELECT Val FROM #Test WHERE Type = 'Int' ) IntsOnly WHERE IntsOnly.Val <= 9.00 ``` This gives me an arithmetic overflow error. However, if I exclude the row of data with the value '10': ``` SELECT * FROM ( SELECT Val FROM #Test WHERE Type = 'Int' AND Val <> '10' ) IntsOnly WHERE IntsOnly.Val <= 9.00 ``` It works without any issue. My question is not how to fix this as I know I can simply convert the data to the format I require. My question is why the value of '10' in the column 'Val' is returning an error. Surely the logic should just return 'False' and simply exclude the rows because '10' (which I assume is implicitly converted) is greater than 9.00. Thanks.