CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

Arithmetic overflow error converting float error in sql

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]

Upvotes: 14

Views: 108150

Answers (1)

rory.ap
rory.ap

Reputation: 35290

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.

Upvotes: 26

Related Questions