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?
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
NUMERIC(9, 2)... 7 digits before and 2 after. Be advised that the result will be rounded.you can use round() after casting it as float or money
select round(cast('4236575.328000000000000000' as money), 2)
Output
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.
numeric(10,4)means: 10 digits in total, thereof 4 after the decimal point - this leaves only 6 digits before the decimal point, and4236575.320needs at least 7 digits before the decimal point. Try to usenumeric(15,4)or something like that - should work no problem.