472,141 Members | 1,336 Online

# 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
Jul 23 '05 #1
2 2999
"Matias Silva" <ma**@nospam.com> wrote in message
news:11*************@corp.supernews.com...
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))

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

Jul 23 '05 #2
Thomas Bartkus wrote:
"Matias Silva" <ma**@nospam.com> wrote in message
news:11*************@corp.supernews.com...
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))

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

Thanks for straightening me out, your absolutly right. When
I removed the TRUNCATE it works.

Thanks for help,
Matias
Jul 23 '05 #3

### This discussion thread is closed

Replies have been disabled for this discussion.