Menu
Why something as simple as
SELECT ROUND(0.99535, 2)
returns an Arithmetic overflow error in SQL SERVER 2008 R2?
286
An arithmetic overflow occurs when the output of an operation is a value bigger than can be stored in the destination data type.
The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.
This error occurs because the action is performed in the database, and the database data type is too small. To avoid this problem, increase the size of the the database data type.
An arithmetic overflow is the result of a calculation that exceeds the memory space designated to hold it. For example, a divide-by-zero yields a much larger result. See arithmetic underflow.
The final datatype is DECIMAL(5,5) so there is no place for 1.0 value.
SELECT ROUND(0.99535, 2)
-- it would round to 1.0000 but it is to big for DECIMAL(5,5)
<=>
SELECT CAST(1 AS numeric(5,5))
-- Arithmetic overflow error converting int to data type numeric.
Checking metadata:
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set
(N'SELECT ROUND(0.99535, 2) AS result', null, 0) ;
-- name system_type_name
-- RESULT numeric(5,5)
DBFiddle Demo
To avoid this problem you could change the precision to 6.
SELECT ROUND(CONVERT(NUMERIC(6,5), 0.99535),2)
DBFiddle Demo 2
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
