Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21,Visits: 9

Hi!

I'm in extreme need of T-SQL function wich will round to the nearest even number, called banker's rounding or rounding to nearest (like Math.Round in .NET)

There are lots of discussions about rounding and realizations of banker's rounding in C#,VB,C++ but I haven't found any realization in SQL. Using exdended procedure is a solution but it's not to my liking.

Group: General Forum Members Last Login: Today @ 9:09 AM Points: 38,559,Visits: 35,570

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes: "Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21,Visits: 9

Thanks Jeff. But none of them is banker's rounding.

Banker's rounding bahaves like arithmetic rounding with one exception: if value is halfway between two numbers, one of which is even and the other odd, then the even number is returned.

Examples of banker's rounding (results of Math.Round in .NET): Math.Round(3.454,2) -> 3.45 Math.Round(3.455,2) -> 3.46 Math.Round(3.445,2) -> 3.44 Math.Round(3.456,2) -> 3.46

Pay your attention on lines 2 and 3. Result depends on wether the second digit after dot is even or odd.

Complexity is that I perform calcualtions with double and finaly need to round the result. So values can have lots of digits after dot and I have to round them one by one to the precision digit.

Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21,Visits: 9

That's realy baker's rounding but with one weakness - it rounds only one digit before precision digit. For instance: rounding of 3.445657545 to second digit after dot takes no account of digits on the right. It should round digit by digit for the rightest to the precision digit. That's the main problem!

DECLARE @Number MONEY DECLARE @Precision INT SET @Precision = 2

SET @Number = 3.454 SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.455 SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.445 SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.456 SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes: "Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Group: General Forum Members Last Login: Tuesday, May 29, 2012 11:22 AM Points: 1,755,Visits: 4,652

Jeff - I think that may not work for the 3.445657545 example (which should go to 3.45).

Here's an alternative (non-looping) solution:

CREATE FUNCTION dbo.RoundBanker (@x money, @DecimalPlaces tinyint) RETURNS money AS BEGIN

set @x = @x * power(10, @DecimalPlaces)

return case when @x = floor(@x) then @x else case sign(ceiling(@x) - 2*@x + floor(@x)) when 1 then floor(@x) when -1 then ceiling(@x) else 2*round(@x/2,0) end end / power(10, @DecimalPlaces)

Group: General Forum Members Last Login: Yesterday @ 4:37 PM Points: 2,314,Visits: 4,284

Jeff and Ryan, try your solutions with these four test cases. The differences between the pairs is in the second decimal position number being odd or even.

The difference between a pairs of numbers is in the fourth decimal position where the first is less than 5 and the second is greater than 5.

You cannot post new topics. You cannot post topic replies. You cannot post new polls. You cannot post replies to polls. You cannot edit your own topics. You cannot delete your own topics. You cannot edit other topics. You cannot delete other topics. You cannot edit your own posts. You cannot edit other posts. You cannot delete your own posts. You cannot delete other posts. You cannot post events. You cannot edit your own events. You cannot edit other events.

You cannot delete your own events. You cannot delete other events. You cannot send private messages. You cannot send emails. You may read topics. You cannot rate topics. You cannot vote within polls. You cannot upload attachments. You may download attachments. You cannot post HTML code. You cannot edit HTML code. You cannot post IFCode. You cannot post JavaScript. You cannot post EmotIcons. You cannot post or upload images.