Menu
I am getting this error
Arithmetic overflow error converting float to data type numeric
when I try to run my view but not sure what am I doing wrong with my calculation. I have researched but could not solve it so far.
Here is the line of code that is causing the error:
ISNULL(CAST(CAST(TOTAL_APPTS.APPT_CNT AS FLOAT) / TOTAL_RECS.PAT_CNT AS NUMERIC(3, 2)), 0) AS [CONVERSION RATE]
801
The SQL Server throws the error because we are trying to store 1000 but the maximum value a NUMERIC (5,2) can hold is 999 before the decimal point. You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.
The solution to avoid Arithmetic overflow error converting expression is to use a bigger 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.
The error "Arithmetic overflow error converting IDENTITY to data type int" comes when the IDENTITY value is inserted into a column of data type int, but the value is out-of-range.
Float stores an approximate value and decimal stores an exact value. In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float. When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.
Your precision and scale arguments to NUMERIC are very small. Have you tried increasing those? Your numeric value can only handle numbers up to 9.99.
You should peruse this page:
decimal and numeric (Transact-SQL)
It's too much to explain here, but basically the first argument (precision) is the max number of digits (in your case 3) and the second argument (scale) is the number of digits to the right of the decimal point, which always takes away from the number of digits you can have to the left of the decimal point. So in your case, 3-2 = 1 digit allowed to the left of the decimal point, which is why your max value can only be 9.99.
106
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
