14

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]
Improve this question

1 Answer 1

Reset to default
26

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

i am not sure how to do that...if you please can elaborate that would be helpful. thnx
just change 'NUMERIC(3,2)' to something like 'NUMERIC(30,15)'.
i think i got it what u were saying and that was the issue. thnx
in .NET, the datatype was double. In SQL Server, it was decimal(5,2) and caused this error. I changed to float in SQL Server and works like a charm.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.