Rounding Up

Since my last post, some people asked me "Well.. great... how do I round up?" Particularly since SQL doesn't do the same rounding as the CLR. SQL's default (and only) rounding algorithm is Rounding Up.

i.e.
SELECT ROUND(2.45, 2), ROUND(2.45, 1)
GO
SELECT ROUND(3.45, 2), ROUND(3.45, 1)
GO
SELECT ROUND(-2.45, 2), ROUND(-2.45, 1)
GO
SELECT ROUND(-3.45, 2), ROUND(-3.45, 1)
GO

Returns:
------- -------
2.45 2.50
3.45 3.50
-2.45 -2.4
-3.45 -3.4

In order to achieve the same rounding as SQL from .NET, you can simply write something like this:

     public static double MathRoundUp(double d, int decimals) 
    { 
        if (d < 0) 
        { 
            double pow = Math.Pow(10, (decimals + 2) * -1); 
            d += pow; 
        } 
        d = Math.Round(d, decimals, MidpointRounding.AwayFromZero); 
        return d; 
    }

 If you have a better algorithm, send it to me!

Comments

• Anonymous
  January 27, 2010
  Hi Kathy, would you advise how to update values in a SQL table in order to round up numbers from 3 decimal places to 2? My email: sharon.wu@verizonwireless.com. Many thanks. Sharon