Author: Community Captain Björn Reuber is a Tech Lead at Board
When working with numeric values, it is important to understand that not every decimal number can be stored exactly in a computer. In my role, I have experienced this challenge on numerous occasions.
Many systems use floating-point numbers to store numeric values. A Single precision number has approximately 6–9 significant digits of precision. This can cause unexpected results when values require more digits, or when calculations rely on an exact comparison.
Dates Stored as Numbers
A date such as 20240131 already contains eight digits.
With Single precision, large integer values cannot always be represented exactly. A value may be rounded internally to the nearest representable number. This makes Single precision unsuitable for storing dates as numeric values when the full eight-digit date representation must be preserved exactly.
Where possible, store dates using a proper Date data type rather than a numeric value.
Using Double Precision
Using Double precision reduces the risk, but it does not remove it.
A Double can store approximately 15–17 significant digits, compared with approximately 6–9 for a Single. This makes it suitable for much larger values and more precise calculations. However, Double precision still uses binary floating-point representation, so decimal values such as 0.1, 0.2, and 0.3 are usually stored as approximations.
Therefore, a calculation may still return a very small difference instead of the mathematically expected result.
Why a Result May Not Be Exactly Zero
Some decimal values, such as 0.1, cannot be represented exactly in binary floating-point format. The stored value is therefore a very close approximation.
This is often invisible in reports because the displayed value is rounded. However, it can become visible in formulas, comparisons, or data validations.
For example, in Excel:
=1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1
Mathematically, the result should be 0.
However, Excel may internally return a very small value, such as:
0.00000000000000014
It may be displayed as 0.00, but it is not necessarily equal to zero.
As a result, the following test can return FALSE:
=(1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1)=0
The Order of Calculations Can Matter
Floating-point calculations are not always associative. In other words, changing the order in which the calculation is performed can produce a slightly different result.
For example, these Excel formulas should both mathematically return zero:
=0.3-0.2-0.1
=0.3-(0.2+0.1)
However, when shown with sufficient decimal places, the first formula can result in:
-0.0000000000000000278
while the second can result in:
-0.0000000000000000555
Both values are extremely close to zero, but neither is exactly zero. The difference is caused by the internal approximations of 0.1, 0.2, and 0.3, together with the sequence in which the operations are performed.
Recommended Approach
Avoid testing floating-point results for exact equality when calculations include decimal values.
Instead of checking whether a value is exactly zero:
Result = 0
Check whether it is close enough to zero:
ABS(Result) < 0.000001
The tolerance should be selected according to the business requirement and the expected scale of the data.
For financial values, or wherever exact decimal precision is required, use an appropriate decimal or currency data type whenever available.
Floating-point numbers are efficient and suitable for many calculations. The important point is to remember that a displayed value of 0 is not always an internally stored value of exactly 0.
