Math.Round and SQL Server Round

Discussion in 'Microsoft VB .NET' started by Guest, Nov 29, 2004.

  1. Guest

    Guest Guest

    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

    Through online help I have read that the VB.NET way of doing it is "called
    rounding to nearest, or banker's rounding."

    I guess my first question is what is correct (when determining Tax or Sale
    prices)?

    My next question is what is the best approach for standarizing? Can I make
    SQL Server act like VB.NET or VB.NET act like SQL Server?

    Are there additional functions either in SQL Server or VB.NET that I am
    missing?

    Also it appears as if the behavior is consistent throughout the VB.NET
    environment (formatcurrency and formatpercent).

    There are certain parts of the applicaiton where it makes sense for SQL
    Server to calculate some of the numbers and other where it makes sense for
    VB.NET to.

    Thanks
     
    Guest, Nov 29, 2004
    #1
    1. Advertisements

  2. Guest

    David Gugick Guest

    tmeister wrote:
    >
    > I guess my first question is what is correct (when determining Tax or
    > Sale prices)?
    >


    Your accountant / CFO should be able to tell you which is the correct
    accounting method. Why is this calculation being left to the
    programmers? This is a financial matter, not a programming one.

    --
    David Gugick
    Imceda Software
    www.imceda.com
     
    David Gugick, Nov 29, 2004
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    I realize this is an accountant question, but if I'm playing every role, it's
    now my decision. I can't imagine that this type of question is not addressed
    with almost every ecommerce site on the Internet. Once a decision is made, I
    will need to know how to implement the appropriate functionality either on
    SQL Server or insided VB.NET.

    Thanks

    "David Gugick" wrote:

    > tmeister wrote:
    > >
    > > I guess my first question is what is correct (when determining Tax or
    > > Sale prices)?
    > >

    >
    > Your accountant / CFO should be able to tell you which is the correct
    > accounting method. Why is this calculation being left to the
    > programmers? This is a financial matter, not a programming one.
    >
    > --
    > David Gugick
    > Imceda Software
    > www.imceda.com
    >
    >
     
    Guest, Nov 29, 2004
    #3
  4. "tmeister" <> wrote in message
    news:...
    > 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
     
    James Goodwin, Nov 29, 2004
    #4
  5. Guest

    David Gugick Guest

    tmeister wrote:
    > I realize this is an accountant question, but if I'm playing every
    > role, it's now my decision. I can't imagine that this type of
    > question is not addressed with almost every ecommerce site on the
    > Internet. Once a decision is made, I will need to know how to
    > implement the appropriate functionality either on SQL Server or
    > insided VB.NET.
    >
    > Thanks
    >


    My point is if you are in charge, you should not take the word of anyone
    but an accountant or someone else who is in the know. The fact that SQL
    Server and VB round differently is not really an issue until you know
    how you must process the data using certified accounting principles.

    --
    David Gugick
    Imceda Software
    www.imceda.com
     
    David Gugick, Nov 29, 2004
    #5
  6. Guest

    Steve Kass Guest

    James Goodwin wrote:

    >"tmeister" <> wrote in message
    >news:...
    >
    >
    >>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.
    >
    >

    James,

    This is called "Banker's Rounding", and is widely used in finance
    (http://support.microsoft.com/kb/196652). It may be required by law in
    some places. There is no "correct" way to round the last digit away
    when a number ends in 5.

    Steve Kass
    Drew University

    >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
    >
    >
    >
    >
     
    Steve Kass, Nov 29, 2004
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tom Gurath
    Replies:
    5
    Views:
    305
    Tom Gurath
    Jan 12, 2004
  2. Test User

    math.round problem

    Test User, Apr 2, 2004, in forum: Microsoft VB .NET
    Replies:
    6
    Views:
    396
    Guest
    Apr 2, 2004
  3. mrmagoo
    Replies:
    5
    Views:
    482
    Jim Hughes
    Mar 29, 2006
  4. Altman

    math.round doesn't work

    Altman, Nov 2, 2006, in forum: Microsoft VB .NET
    Replies:
    3
    Views:
    404
    Altman
    Nov 2, 2006
  5. fniles
    Replies:
    9
    Views:
    798
    Erland Sommarskog
    Dec 19, 2007
Loading...

Share This Page