Rounding errors with the ROUND()

Can anybody tell me why I am getting rounding errors
using the ROUND function.

3.7125 rounds to 3.70 when I use the following:
TRUNCATE(ROUND(units_pay_amount * fees_amount, 2),2)))
The correct value should be 3.71

I could round to the 3rd decimal place ROUND(X,3) and
that would round it correctly to 3.71 but that would
mean I would have to change the ROUND function in another
part of the query in order to make it uniform (to 3 decimal
places).

The kick in the ass is if I try to make it uniform (to 3
places) then another value is wrong and when this one
is right...

Appreciate any help, thanks,
Matias

"Matias Silva" wrote in message

 > Can anybody tell me why I am getting rounding errors
 > using the ROUND function.
 >
 > 3.7125 rounds to 3.70 when I use the following:
 > TRUNCATE(ROUND(units_pay_amount * fees_amount, 2),2)))
 > The correct value should be 3.71
 >
 > I could round to the 3rd decimal place ROUND(X,3) and
 > that would round it correctly to 3.71 but that would
 > mean I would have to change the ROUND function in another
 > part of the query in order to make it uniform (to 3 decimal
 > places).
 >
 > The kick in the ass is if I try to make it uniform (to 3
 > places) then another value is wrong and when this one
 > is right...

Why are you blaming ROUND()?

It appears that it is TRUNCATE() that is a bit funky and ROUND() does indeed
give the correct results according to it's definition.

ROUND(3.7125,2) = 3.71 <- correct
TRUNCATE(3.71, 2) = 3.70 <- *wrong* according to TRUNCATEs definition.

And - Since ROUND is a truncation to the number of decimal places you
specified anyway, why in heck do you need to TRUNCATE?

ROUND(x, n)
should *always* return the exact same value as the needlessly complicated -
TRUNCATE(ROUND(x, n), n))
because you have already discarded your extraneous digits using ROUND alone.

So why deal with the TRUNCATE() bug?
Even if it returned the correct result, it does nothing for you!

Although we do like to know about these bugs
Thomas Bartkus