KnowledgeBase Content Details

July 06, 2021

Question:

Using a Real data type and the Round() function can sometimes give an incorrect result.

Answer:

When calculating Round() using the value 70.445 as an example and specifying 2 decimals, will give you 70.44 if the column used is a Real and 70.45 if the column type is Currency, where the latter one is correct.

The reason for this is due to the way numerical columns are handled using floating point arithmetic.

To make sure that the values are correct when using the Round() function, please use columns with the data type Currency as it is more precise than the Real data type.

Below are some links that discusses the reason for this in detail.

How numbers differ when converting to floating point:
https://www.exploringbinary.com/floating-point-converter/

Discussion around floating point arithmetic:
https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

Discussion about the accuracy issues:
https://en.wikipedia.org/wiki/Floating-point_arithmetic#Minimizing_the_effect_of_accuracy_problems

Back to KnowledgeBase