Arithmetic overflow error converting varchar to data type numeric in SQL Server

0
votes

I getting an "Arithmetic Overflow" error when trying this conversion:

select convert(numeric(10, 4), '4236575.320000000000000000')

But it can convert for numeric without decimal by using this:

select convert(numeric, '4236575.320000000000000000')

What would be the reason for this?

3
sqlsql-servertsqltype-conversionnumeric
numeric(10,4) means: 10 digits in total, thereof 4 after the decimal point - this leaves only 6 digits before the decimal point, and 4236575.320 needs at least 7 digits before the decimal point. Try to use numeric(15,4) or something like that - should work no problem. - marc_s

3 Answers

5
votes

The value 4236575.320000000000000000 requires a datatype of NUMERIC(25, 18) because you have 7 digits before decimal and 18 after it.

Having said that, you can use smaller precision and scale as long as the portion before decimal could be stored without truncation:

SELECT str
     , CONVERT(NUMERIC(8, 1), str) AS [ddddddd.d]
     , CONVERT(NUMERIC(7, 0), str) AS [ddddddd]
     , CONVERT(NUMERIC(9, 2), str) AS [ddddddd.dd]
FROM (VALUES
    ('4236575.320000000000000000')
) AS tests(str)

Result:

str                        | ddddddd.d | ddddddd | ddddddd.dd
4236575.320000000000000000 | 4236575.3 | 4236575 | 4236575.32
1
votes

you can use round() after casting it as float or money

select round(cast('4236575.328000000000000000' as money), 2)

Output

enter image description here

1
votes

When you specify numeric(10,4) you are telling SQL Server to use a precision of 10 i.e. the number of digits stored from both sides of the decimal point and a scale of 4 i.e 4 digits to the right of the decimal point.

This effectively means that the maximum number can be 999999.9999 (with 6 digits to the left of the decimal point and 4 digits to the right) which is smaller than 4236575.3280 which would overflow the storage. Importantly the data that would be lost is the most significant digit rather than the least significant digit and this is why it fails.

You will notice that the following all succeed:

select convert(numeric(11, 4), '4236575.320000000000000000')
select convert(numeric(10, 3), '4236575.320000000000000000')
select convert(numeric(8, 1), '4236575.320000000000000000')

which all allow for 7 digits to the left of the decimal point.