Re: Math.Round and SQL Server Round

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: James Goodwin (jim.goodwin_at_midmichigan.org)
Date: 11/29/04 

Date: Mon, 29 Nov 2004 16:07:43 -0500

"tmeister" <tmeister@discussions.microsoft.com> wrote in message
news:568352DA-3AAF-4252-8CEA-5DFF4A504A24@microsoft.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

Relevant Pages

  • Re: Math.Round and SQL Server Round
    ... > I'm trying to determine the best approach for rounding in an application ... Unfortunately it appears as though SQL Server and VB.NET round ... To Cause VB to round in a normal way take the int of +.5 so for the numbers ... DECLARE @Num as Numeric ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Saving DateTime to Sql Server 2005
    ... rounding in Math doesn't seem to round the same way Sql server does... ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Math.Round and SQL Server Round
    ... >>I'm trying to determine the best approach for rounding in an application ... Unfortunately it appears as though SQL Server and VB.NET round ... >Declare @Dig2 as Integer ...
    (microsoft.public.sqlserver.programming)
  • Re: Math.Round and SQL Server Round
    ... >>I'm trying to determine the best approach for rounding in an application ... Unfortunately it appears as though SQL Server and VB.NET round ... >Declare @Dig2 as Integer ...
    (microsoft.public.dotnet.languages.vb)
  • Re: XQ and ->Qpi bug on large X
    ... you shouldn't be rounding to only two digits ... I am trying to make a similar point here about rounding; ... and round it, ... prior to using the input values in calculations. ...
    (comp.sys.hp48)