July 16, 2007 at 5:23 am
I have not found any function in SQL Server 2005 to peform bankers rounding.
You do have one in the Dot Net Framework
February 21, 2013 at 7:22 am
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?
February 21, 2013 at 8:04 am
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
August 10, 2023 at 8:30 pm
Cross-post from another thread, more details there:
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)
AS
BEGIN
RETURN CASE
WHEN ROUND(@Num, 2) - @Num = .005 AND ROUND(@Num, 2) % .02 <> 0
THEN ROUND(@Num, 2) - 0.01
ELSE ROUND(@Num, 2)
END
END
Viewing 4 posts - 376 through 378 (of 378 total)
You must be logged in to reply to this topic. Login to reply