4

I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is causing my equation to return an incorrect value.

Can someone explain to me why

SELECT 776.2384 * cast(100 as real) / 100 

equals 776.2385 while

SELECT 776.2384 * 100 / 100

equals 776.2384?

5

1 Answer 1

4

See Using decimal, float, and real Data, and Data Type Precedence

In your second query, 776.2384 is a decimal number (and so are the two other values). So the calculation is done in that type, and the result is mathematically exact.

In the first, you introduce a real. The other literals are promoted to that type, and the calculation is done according to IEEE 754 rules, in round up mode. IEEE 754 floating points are not exact, so you will generally not obtain the "mathematically exact" result you're expecting.

Quote from the articles above:

Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

2
  • So.... its actually storing the number as something like 776.238400000000001, and rounding up to 776.2385? Strangely enough, doing the division before the multiplication actually returns the correct result. I also get the correct result when only doing the multiplication, or only doing the division. Thank you though, I think I have a better understanding of what Real is and why its not accurate (although I had to Google IEEE 754 rules) :)
    – Rachel
    Commented Sep 13, 2012 at 14:41
  • In trying to solve doing division and getting imprecise results, cast float / cast float is drastically more correct than cast real / cast real Commented Jan 10, 2020 at 17:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.