Round to Even (aka Banker''s Rounding) - The final function

• I have not found any function in SQL Server 2005 to peform bankers rounding.

You do have one in the Dot Net Framework

Math.Round Method (Decimal, MidpointRounding)

If you are using SSRS you can use framework functions in the Report Designer.

One work around that worked for me is to increase the precision to 3-4 digits more than required ( I needed 6 , the i used 10 digits ) and then round the result. This does not work if the summation is for thousands of rows.
• Two questions please, having ploughed my way through this lesson in trolling:

1. Was there a final version of the banker's rounding algorithm which rounded correctly to 5 decimal places any "representation of a number" (to avoid concerns over imprecise representations of numbers) stored in a decimal(38,12) field? I have a real-world need, where one of our systems connects with another which is doing this rounding, and the SQL database is not.

I have an algorithm already (based I believe on one of those in this thread) which uses the power() function, and works fine except that this breaks down once the data being rounded is greater than 92,233,720,368,547.8 (rounded up from .746something), which is obviously less than the maximum possible in the decimal field. The example that I am failing on is 99999999999999.123565 to 5 decimal places, which should of course be 99999999999999.12356

2. We have clients based in New Zealand. Will they be arrested?

• Urrgh

I have just identified the issue as being with my function, and one that I corrected elsewhere some time ago. Please ignore.

For reference, below is the function being used, which handles the two cases posted on the first page of this thread as well as our regular requirement (rounding to 5 places)

`CREATE FUNCTION dbo.RoundBanker (@val decimal(38,12), @pos int)`

`RETURNS decimal(38,12)`

`as `

`-- Takes two parameters. First is number to be rounded, second is how many places to round to`

`---------------------------------------------------------------------------------------------`

`begin`

`declare`

`@tmpval1bigint,`

`@tmpval2decimal(38,12),`

`@retvaldecimal(38,12),`

`@tmpval3decimal(38,12),`

`@tmpval4decimal(38,12),`

`@predecdecimal(38,12),`

`@postdecdecimal(38,12)`

`-- To get most from function, ignore everything before decimal point`

`--------------------------------------------------------------------`

`set @predec = floor(abs(@val))`

`set @postdec = case sign(@val) when 1 then @val - @predec else @val + @predec end `

`-- Actual work (Lynn's)`

`----------------------`

`set @tmpval1 = floor(abs(@postdec) * power(cast(10 as float), @pos))`

`set @tmpval2 = round(@postdec, @pos, 1)`

`set @tmpval3 = sign(@postdec) * (0.5 * power(cast(10 as float), (-1 * @pos)))`

`set @tmpval4 = (@postdec - @tmpval2)`

`set @retval = round(@postdec, @pos, case`

`when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null`

`and ((@tmpval3 >= @tmpval4 and sign(@val) = 1)`

`or (@tmpval4 >= @tmpval3 and sign(@val) = -1))`

`then 1`

`else 0`

`end)`

`-- Rebuild number`

`-----------------`

`select @retval = case sign(@val) when 1 then @retval + @predec else 0.0 - @predec + @retval end`

`return @retval`

`end`

• Cross-post from another thread, more details there:

https://www.sqlservercentral.com/forums/topic/bankers-rounding-in-t-sql-like-math-round-in-net/page/4#post-4258087

This is the function we currently use, with some assumptions and simplifications.

We only need to represent values below 1M and we always round to 2 decimal places.

`CREATE FUNCTION dbo.fnBankersRound(@Num decimal(10,4))RETURNS decimal(8,2)ASBEGINRETURN CASE        WHEN ROUND(@Num, 2) - @Num = .005 AND ROUND(@Num, 2) % .02 <> 0        THEN ROUND(@Num, 2) - 0.01        ELSE ROUND(@Num, 2)     ENDEND`

Viewing 4 posts - 376 through 378 (of 378 total)