SQLite Forum

change in round() function
Login
Well, double precision floating point has *always* been sufficiently accurate for financial calculation to the 1/100th of a unit, assuming that no "value" or intermediate "value" exceeds the precision capabilities of a double -- that is, provided that no value or intermediate exceeds (+/-)351,843,720,888.00 units, which has a precision greater than 1.0e-04.

The "old wives tale" about not using base-2 floating point for money has been perpetuated by a bunch of people who do not understand computers nor binary or computational theory.

The biggest problem in using floating point (no matter the precision), decimal floating point, or scaled decimal arithmetic is rounding of intermediates.  Once you have addressed the rounding issue, Bob's your Uncle.  If you haven't yet figured out how to address rounding, then nothing will help you.  There are also other pathological problems when dealing with floating point, none of which have material impact if you are constraining the magnitude of the numbers you are playing with.

That said, the round() and double-to-text functions (printf) in SQLite are not suitable for financial applications.  They do "Nearest Half-Away-from-Zero" rounding, not "Nearest Half-to-Even" or "Nearest Half-to-Odd" rounding.

The simplest implementation of Nearest Half-to-Even rounding can be implemented as follows (assuming of course that your compiler and platform implements IEEE standards properly):

```
double roundhalfeven(double x, int digits)
{
   double scale = pow(10.0, digits);
   return 2.0 * round(x / 2.0 * scale) / scale;
}
```

Bounds checking is left as an exercise for the reader.

see also <https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html>

As noted, the biggest influence on the use of IEEE floating point to deal with currency are (a) failure to understand precision and computation theory (b) failure to understand and perform correct rounding; and, (c) junk ass compilers.