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!

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