Rounding Money

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))

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

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.

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