Home Forums SQL Server 7,2000 T-SQL Banker''s rounding in T-SQL (like Math.Round in .NET) RE: Banker''''s rounding in T-SQL (like Math.Round in .NET)

  • If you mean round to the nearest whole number, then simply converting to INT will do...

    DECLARE @Amount1 MONEY

        SET @Amount1 = 7.49

    DECLARE @Amount2 MONEY

        SET @Amount2 = 7.50

    DECLARE @Amount3 MONEY

        SET @Amount3 = 7.51

    SELECT CAST(@Amount1 AS INT)

    SELECT CAST(@Amount2 AS INT)

    SELECT CAST(@Amount3 AS INT)

    If you really mean to round to the nearest EVEN number (as you say, Banker's rounding), that is, the nearest number evenly divisible by 2, then this will do (the forumula)...

    DECLARE @Amount1 MONEY

        SET @Amount1 = 6.01

    DECLARE @Amount2 MONEY

        SET @Amount2 = 6.99

    DECLARE @Amount3 MONEY

        SET @Amount3 = 7.00

    SELECT CAST(@Amount1/2 AS INT)*2

    SELECT CAST(@Amount2/2 AS INT)*2

    SELECT CAST(@Amount3/2 AS INT)*2

    ...and it appears to work correctly with negative numbers, as well...

    DECLARE @Amount1 MONEY

        SET @Amount1 = -6.01

    DECLARE @Amount2 MONEY

        SET @Amount2 = -6.99

    DECLARE @Amount3 MONEY

        SET @Amount3 = -7.00

    SELECT CAST(@Amount1/2 AS INT)*2

    SELECT CAST(@Amount2/2 AS INT)*2

    SELECT CAST(@Amount3/2 AS INT)*2

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)