"tmeister" <tm******@discussions.microsoft.com> wrote in message

news:56**********************************@microsof t.com...

I'm trying to determine the best approach for rounding in an application
I'm building. Unfortunately it appears as though SQL Server and VB.NET round
in different ways.

SQL Server

select round(123.465,2)

returns

123.470
Which I think is correct.
VB.NET

Math.Round(123.465, 2)

returns

123.46

VB Rounds a 5 to the nearest EVEN number so:

123.465 becomes 123.46 while 123.475 becomes 123.48 I personally consider

this to be an incredibly inconsistent form of rounding and find that is

causes numerous issues when programming. As far as I know only Microsoft

rounds this way and it might only be VB.

To Cause VB to round in a normal way take the int of +.5 so for the numbers

above Int((Num*100)+.5))/100 Or if the Floor function takes an argument for

the number of decimals Floor(Num+.005,2) I don't use VB much so I'm not

sure about the Function Names.

To Make SQL round the VB way is trickier, you need to determine whether the

rounding digit(s) = 5 and then if the digit before the rounding digit is

even or odd. Something like:

DECLARE @Num as Numeric(8,4)

Declare @Dig as Numeric(8,4)

Declare @Dig2 as Integer

Declare @Even as integer

SET @Num = 123.465

SET @Dig = @Num * 1000 - (CAST(@Num*100 as integer)*10)

SET @Dig2 = CAST(@Num * 100 as Integer) - (CAST(@Num * 10 as Integer)*10)

SET @Even = Case When @Dig2 in (2,4,6,8,0) then 1 else 0 end

SELECT Round(Case @Dig When 5 THEN CASE @Even When 1 THEN @Num-0.001

ELSE @Num+.001 END

ELSE @Num END,2)

Regards,

Jim