Banker''s rounding in T-SQL (like Math.Round in .NET)

Question

Post reply

Banker''s rounding in T-SQL (like Math.Round in .NET)

Viewing 2 posts - 46 through 46 (of 46 total)

You must be logged in to reply to this topic. Login to reply

CodeMuddler SSC Eights! Points: 925 More actions · Answer 1

Just realized the inner case statement was over complicated and I had a WHEN that could never be satisfied..

revised:



CREATE FUNCTION dbo.itvf_BankersRound(@Num decimal(38,9))

RETURNS TABLE

AS

RETURN

(

	SELECT

		CASE

			WHEN

				ROUND(@Num, 2) - @Num = .005

				AND ROUND(@Num, 2) % .02 <> 0

				THEN

					ROUND(@Num, 2) - 0.01

			ELSE ROUND(@Num, 2) END AS [Result]

)

GO

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 2

The default rounding method in .NET is Round-to-Even (or 'Bankers Rounding' as described in this thread). This type of rounding can be implemented in SQL Server quickly, easily and accurately using a CLR function that returns a value by simply using the .NET Decimal.Round method.

I've tested such a CLR function against around 15 million values of datatype 'money' and the query completed in around 25s. I tried the first solution in this thread and I decided to stop the query after 10 minutes. In my situation the CLR solution wins the race.

Chris