Nathan Riley

asked on

# Rounding Money

I need to round the following:

2.7500

2.3600

-0.0002

-4.1200

5.364

7.376

to:

2.75

2.36

0

-4.12

5.36

7.38

2.7500

2.3600

-0.0002

-4.1200

5.364

7.376

to:

2.75

2.36

0

-4.12

5.36

7.38

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
Use decimal: http://msdn.microsoft.com/en-us/library/ms187746.aspx

ROUND(your_col, 2) will work if you don't need to do any math on it down stream.

If you need to actually convert the value for later calculations, I do the following:

((FLOOR(ABS((your_col * 100) + 0.5)) / 100) * SIGN(your_col))

If you need to actually convert the value for later calculations, I do the following:

((FLOOR(ABS((your_col * 100) + 0.5)) / 100) * SIGN(your_col))

jjworld, what is the difference between your complicated code and round( x, 2 )? Isn't it what is done inside of round?

Not entirely. That's why I mention both methods. ROUND will not have the same precision that a truncation will. It all depends on the math you need to perform layers later.

jjworld,

1. You obviously meant ((FLOOR( ABS(your_col) * 100 + 0.5 ) / 100) * SIGN(your_col)) - the number must become positive BEFORE it is increased by 0.5.

2. You can test yourself and see there's no difference:

declare @i int,

@r real,

@r_round real,

@r_trunc real,

@msg varchar(100)

set @i = 0

while @i < 10000000

begin

set @r = rand()*1000 - 500

set @r_round = round( @r, 2 )

set @r_trunc = ((FLOOR( ABS(@r) * 100 + 0.5 ) / 100) * SIGN(@r))

if @r_round != @r_trunc

begin

set @msg = 'round( ' + convert( varchar, @r, 2 ) + ' ) = ' + convert( varchar, @r_round ) +

', while truncation results in ' + convert( varchar, @r_trunc )

print @msg

end

set @i = @i + 1

end

1. You obviously meant ((FLOOR( ABS(your_col) * 100 + 0.5 ) / 100) * SIGN(your_col)) - the number must become positive BEFORE it is increased by 0.5.

2. You can test yourself and see there's no difference:

declare @i int,

@r real,

@r_round real,

@r_trunc real,

@msg varchar(100)

set @i = 0

while @i < 10000000

begin

set @r = rand()*1000 - 500

set @r_round = round( @r, 2 )

set @r_trunc = ((FLOOR( ABS(@r) * 100 + 0.5 ) / 100) * SIGN(@r))

if @r_round != @r_trunc

begin

set @msg = 'round( ' + convert( varchar, @r, 2 ) + ' ) = ' + convert( varchar, @r_round ) +

', while truncation results in ' + convert( varchar, @r_trunc )

print @msg

end

set @i = @i + 1

end

My bad, on the fly coding within the comment box.

You are looking at the result within the context of a single procedure. Build a view with each calculation and then examine the results when aggregating off the calculations. You will find different levels of precision as you move further away from the original calculation.

I only added the tip for the case where the person providing the question wasn't asking a simple calculation question but a deeper implication that the results were not as intended. There are several situations that can provide erroneous rounding results when decimal and float type data are being considered.

I believe this question is closed, but I'd be happy to provide specific examples in various DBMS products that behave in a similar fashion. Products affected include SQL Server, Oracle 9 and prior, and some flavors of Informix. (and some Sybase revs for any old folk) It's a known issue, I'm not making it up.

You are looking at the result within the context of a single procedure. Build a view with each calculation and then examine the results when aggregating off the calculations. You will find different levels of precision as you move further away from the original calculation.

I only added the tip for the case where the person providing the question wasn't asking a simple calculation question but a deeper implication that the results were not as intended. There are several situations that can provide erroneous rounding results when decimal and float type data are being considered.

I believe this question is closed, but I'd be happy to provide specific examples in various DBMS products that behave in a similar fashion. Products affected include SQL Server, Oracle 9 and prior, and some flavors of Informix. (and some Sybase revs for any old folk) It's a known issue, I'm not making it up.

And of course there is also the question of the definition of rounding. There are as many variations as SQL Server Editions...

http://en.wikipedia.org/wiki/Rounding

http://en.wikipedia.org/wiki/Rounding