‘Bankers’ Algorithm – SQL Function

CREATE FUNCTION dbo.BankersRound(@data DECIMAL(25,10), @digits INT)
RETURNS DECIMAL(25,10) AS
BEGIN
	SET @data = @data * POWER(10, @digits)
	RETURN
		CASE WHEN @data = FLOOR(@data) THEN @data
		ELSE
			CASE SIGN(CEILING(@data) - 2 * @data + FLOOR(@data))
				WHEN 1 THEN FLOOR(@data)
				WHEN -1 THEN CEILING(@data)
			ELSE 2 * ROUND(@data / 2, 0) END
		END / POWER(10, @digits) 
END
GO

Usage

SELECT 
     dbo.BankersRound(2.1, 2) AS r1
    ,dbo.BankersRound(2.11, 2) AS r2
    ,dbo.BankersRound(2.555, 2) AS r3
    ,dbo.BankersRound(2.565, 2) AS r4
    ,dbo.BankersRound(3.915, 2) AS r5
Advertisements