Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00

Question

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.

Arithmetic overflow error, you sure? Not a [conversion error](http://stackoverflow.com/q/9136722/11683)? — GSerg, Oct 31 '12 at 15:25
Try being ***explicit*** in the conversion. What does `CAST(val AS DECIMAL(9,2)) <= CAST(9.00 AS DECIMAL(9,2))` give? — MatBailie, Oct 31 '12 at 15:26
GSerg - Yep, arithmetic overflow converting the varchar to numeric. Dems - That does do the trick! However, I'd like to understand why the implicit conversion does not work? My thought was that it could do that conversion and should not cause an issue? — JBond, Oct 31 '12 at 15:28
This is not a safe way of doing this query anyway. See [SQL Server should not raise illogical errors](http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors). The cast can still get pushed down and happen against the value `Yes` despite the use of the sub query. — Martin Smith, Oct 31 '12 at 15:34

RBarryYoung · Accepted Answer · 2012-10-31T15:42:34.497

18

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

edited Oct 31 '12 at 15:42

answered Oct 31 '12 at 15:32

RBarryYoung

55,398
14
96
137

2

Also what I have noticed is that lets say you have a `Decimal (5,3)` and the number you have is `12.321` that will be accepted. But if your number is `123.321` <- that is `6 digits in total` so wont accept it. Your decimal should then be `Decimal (6,3)`. That is just something I have tested and confirmed myself `SQL 2014` – Pierre Jul 22 '14 at 06:20
3

@Pierre Yes, that's literally just the definition of the `DECIMAL` datatype specification. – RBarryYoung Jul 22 '14 at 06:22

Richard Deeming · Answer 2 · 2012-10-31T15:47:33.847

4

From BOL:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

That means your constant 9.00 will have ~~a precision of 1 and a scale of 0~~ a precision of 3 and a scale of 2, so it cannot store the value 10, which needs a minimum precision of 2 + scale.

You'll need to wrap the IntsOnly.Val with either a CAST or CONVERT to specify the correct precision and scale.

edited Oct 31 '12 at 15:47

answered Oct 31 '12 at 15:31

Richard Deeming

29,830
10
79
151

1

I'm sure you meant to write scale of 2? – Martin Smith Oct 31 '12 at 15:39
It depends how clever the SQL engine is; it could either convert to a `numeric(3, 2)` because there are two digits after the decimal point, or it could convert to a `numeric(1, 0)` because the digits after the decimal point are both `0`. – Richard Deeming Oct 31 '12 at 15:41
It looks at all zeros to the right of the decimal point but does ignore all leading zeros to the left. So `00000.0000` gives `numeric(4,4)` – Martin Smith Oct 31 '12 at 15:42

PRAMOD MALLICK · Answer 3 · 2019-10-29T04:33:40.507

-2

TRY THIS...IT WORKED FOR ME

     CAST(CAST(@UR_VARIABLE AS FLOAT) AS NUMERIC(3,2))

edited Oct 29 '19 at 04:33

answered Oct 01 '19 at 08:41

PRAMOD MALLICK

1
2

Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00

3 Answers3

Linked