There are various ways to round a number, and most of us don’t give it much thought, but we should. There are several methods for rounding: Round Up, Round Down, Round Away From Zero, Round Toward Zero, and Round Toward Even (also known as bankers rounding, unbiased rounding, Gaussian rounding, and statisticians rounding), and Stochastic Rounding.

It’s important to note that in most cases, the Round function produces the result we want. The intended purpose of the code shown below is to affect only those cases where a number can be rounded up or down because the value rounded is exactly mid-way between the rounded up number and the rounded down number. For example, when you round 3.5 to the nearest whole number, what should the result be? 3 or 4? When you perform many calculations on rounded numbers, a Stochastic round or a bankers round will be more accurate than any other method because (statistically), the edge cases will cancel each other out, resulting in a more accurate result.

It’s interesting to note that many client side languages like vb6 and the .net framework implement bankers rounding. Starting with .net framework 2.0, there is an optional 3rd argument to the round function that allows you to modify the behavior. In any event, this differs from SQL Server’s implementation of rounding, which is always ‘Round away from zero’. For example, round(4.25, 1) = 4.3 and Round(-4.25, 1) = -4.3. Both numbers are rounded away from zero. In VB, Round(4.25, 1) = 4.2

Let’s look at some sample data, and the expected results when rounded to 1 decimal place:

Value Up Down Towards Zero Away From Zero Bankers Stochastic -------- ---- ---- ------------ -------------- ------- ---------- 4.15 4.2 4.1 4.1 4.2 4.2 4.1 or 4.2 4.15001 4.2 4.2 4.2 4.2 4.2 4.2 4.25 4.3 4.2 4.2 4.3 4.2 4.2 or 4.3 -4.15 -4.1 -4.2 -4.1 -4.2 -4.2 -4.1 or -4.2 -4.25 -4.2 -4.3 -4.2 -4.3 -4.2 -4.2 or -4.3 -4.25001 -4.3 -4.3 -4.3 -4.3 -4.3 -4.3

**Round Up:**

This method always rounds the number up to the next highest number. For example, 3.1 rounded up is 4 and -3.1 rounded up is -3. In SQL Server, the easiest way to achieve ‘RoundUp’ is to use the ceiling function. Unfortunately, the Ceiling function only works with whole numbers. There’s no built-in function that RoundsUp where you can specify the number of digits. This is no problem, we can just build our own.

Create Function dbo.RoundUp(@Val Decimal(32,16), @Digits Int) Returns Decimal(32,16) AS Begin Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 Then Ceiling(@Val * Power(10,@Digits))/Power(10, @Digits) Else Round(@Val, @Digits) End End

**Round Down:**

This method is similar to rounding up, except it is rounded to the nearest smaller number. For example, 3.1 rounded down is 3, -3.1 rounded down is -4. SQL Server has a floor function that rounds down for up. Just like the ceiling function, the floor function does not accommodate the number of digits (it only works on whole numbers). Another function to the rescue.

Create Function dbo.RoundDown(@Val Decimal(32,16), @Digits Int) Returns Decimal(32,16) AS Begin Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 Then Floor(@Val * Power(10,@Digits))/Power(10, @Digits) Else Round(@Val, @Digits) End End

**Round Away From Zero:**

This method does just that. 3.5 rounds away from zero to 4. -3.5 rounds away from zero to -4. Implementing Round Away From Zero is the simplest one to accommodate in SQL Server. The ROUND function does this already, and it accommodates a varying number of digits, too.

**Round Towards Zero:**

In this method, 3.5 rounds down to 3, but -3.5 rounds up to -3. Implementing Round Towards Zero is relatively simple in SQL Server too. There is an optional 3rd parameter to the Round function that simply truncates the number to the number of decimal places. Unfortunately, this does not check for the edge case where the last digit is 5 followed by zeros.

4.15 should round to 4.1

4.15000001 should round to 4.2

Using the 3rd argument of the round function will blindly truncate the trailing numbers, so both would be rounded to 4.1. This is not what we want, so we’ll need to write another function.

Create Function dbo.RoundToZero(@Val Decimal(32,16), @Digits Int) Returns Decimal(32,16) AS Begin Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 Then Round(@Val, @Digits, 1) Else Round(@Val, @Digits) End End

**Round Toward Even:**

This is known by many different names (Bankers Rounding, unbiased rounding, Gaussian rounding, and statisticians rounding). With this method, special exceptions are made if the last digit to remove is exactly 5. If it is, the number is rounded to the next even number.

Example,

4.15 Bankers Rounded to 1 digits is 4.2

4.25 Bankers Rounded to 1 digits is 4.2

This occurs because everything to the right of the number of digits (5) is exactly 5. Had it been 51, the number would have been rounded up. Less than 50, and the number is rounded down. Since it is exactly 50, we need to examine the digit to the left of it (the 1st decimal place). If it’s an even number, the rounded value is truncated. If it’s an odd number (like the 1 in 4.15), then it’s rounded to the next even number (to become 4.2).

We can write another function in SQL Server to implement bankers rounding.

Create Function dbo.BankersRound(@Val Decimal(32,16), @Digits Int) Returns Decimal(32,16) AS Begin Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 Then Round(@Val, @Digits, Case When Convert(int, Round(abs(@Val) * power(10,@Digits), 0, 1)) % 2 = 1 Then 0 Else 1 End) Else Round(@Val, @Digits) End End

**Stochastic Rounding**

This is similar to ‘Round to even’. When the remaining digits is a 5 followed by zeros, we randomly round down or up. Implementing this is a bit more difficult because SQL Server doesn’t like random numbers in user defined function. To work-around this limitation, we can create a view that returns a random number and call that view from within the function. First, the view:

Create View vw_RandomBit As Select Case When Convert(Char(1), Convert(VarChar(36), NewId())) > '7' Then 1 Else 0 End As RandomBit

Then, we can create this function.

Create Function dbo.StochasticRound(@Val Decimal(32,16), @Digits Int) Returns Decimal(32,16) AS Begin Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 Then Round(@Val, @Digits, Convert(int, (Select RandomBit From vw_RandomBit))) Else Round(@Val, @Digits) End End

I hope the differences in the algorithm is obvious now, and you’ll be able to make a better informed choice regarding which method of rounding to use.

## 13 Comments

There’s an additional type of rounding that is used in item pricing: you always round to the nearest 9. That is, if the price of an item is $5.00 you round it to $4.99. A further example would be rounding a price from $3.47 to $3.99.

I haven’t seen this implemented in T-SQL. Anyone have an idea how to do it?

I was stuggling a bit to implement before seeing this code. Today, I am happy to note this code.

I was looking for the rounding function in sql server 2005 and got the ceiling. But I was surprised that it outputs 4 when provided 3.5 as the input but 3 when provided 7/2 as the input. Anyway all the ideas in this post are really great.

sangam,

When performing math in T-SQL on integers, the result is always an integer. Ex:

Select 7/2

Since the 7 is an integer and the 2 is an integer, the result is 3, which rounds to 3.

If you force the inputs to be a decimal data type, the result is a decimal, which then rounds to 4. Ex:

Select 7.0/2

Select Round(7.0/2, 0)

You’ll get 3.5 for the first select, which rounds to 4.

For more information on this: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-precision-and-scale-problems

I am still surprised at how many programmers do not know about these rounding methods in various languages. Back in the old days, COBOL programmers were very careful.

As an aside, the SQL Standards let rounding and truncation be implementation defined.

hy all

i wana to get the number after “dot” in decimal number

for exampel

1234.5 here i wanna to got just number after “dot” here it’s 5

thank advance

Thanks George.

I may have found a typo… In the RoundUp function… it looks like the =5 should be <5 . That’s the only way I was able to get it to work.

Hello, I’m trying to post a comment and am getting errors:

Cannot post comment, please correct these errors:

Parser error: XML_ERR_NAME_REQUIRED near e ) >= 1

Trying to post a function I created that might save some people some grief. Is there a way to put it in a PRE tag or have the parsing ignore it, I think it’s choking on a greater than or less than sign and thinking it’s a tag.

Ah, I think I figured it out, just have to encode the <> characters.

For those of you who have stumbled upon this looking for a way to do banker’s rounding in MySQL, I will share with you what I use.

CREATE FUNCTION BROUND( value DECIMAL(65,30), places TINYINT(3) UNSIGNED ) RETURNS DECIMAL(65,30) DETERMINISTIC RETURN

CASE WHEN

LOCATE( ‘.’, value ) >= 1

AND LENGTH( value ) < 33

AND places > 0

AND LENGTH( value ) – LOCATE( ‘.’, value ) > places

AND SUBSTRING( value, LOCATE( ‘.’, value ) + places + 1, 1 ) = 5

AND SUBSTRING( value, LOCATE( ‘.’, value ) + places, 1 ) % 2 = 0

THEN

SUBSTRING( value, 1, LOCATE( ‘.’, value ) + places )

ELSE

ROUND( value, places )

END;

Hopefully this saves someone pulling out their hair trying to figure out how to address all the 1 cent errors when comparing MySQL invoice reporting that uses MySQL’s arithmetic ROUND function against what their accounting software that uses banker’s rounding is telling them (i.e. Simply Accounting or many others).

I found out the hard way that if you have it accept and return a varchar, the answer is not precise because the decimal number requires more bytes than the varchar version, and the built-in ROUND function it depends on would then treat it differently in that case.

PLEASE DO NOT USE MY MYSQL FUNCTION ABOVE! IT IS WRONG!

Use this one instead:

CREATE DEFINER=`root`@`%` FUNCTION `BROUND`( value DECIMAL(65,30), places TINYINT(3) UNSIGNED ) RETURNS decimal(65,30) COMMENT ‘WARNING over decimal(65,30) will round normally!’

DETERMINISTIC

RETURN

CASE WHEN

LOCATE( ‘.’, value ) >= 1

AND LENGTH( SUBSTRING( value, LOCATE( ‘.’, value ) +1 ) ) < 31

AND places > -1

AND LENGTH( value ) – LOCATE( ‘.’, value ) > places

AND SUBSTRING( value, LOCATE( ‘.’, value ) + places + 1, 1 ) = 5

AND SUBSTRING( value, LOCATE( ‘.’, value ) + places + 2 ) = 0

AND SUBSTRING( value, LOCATE( ‘.’, value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ), 1 ) % 2 = 1

THEN

SUBSTRING( value, 1, LOCATE( ‘.’, value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ) )

ELSE

ROUND( value, places )

END;

Do not be confused in how it works, here are some examples of how a correct banker’s rounding function should work:

BROUND(6.433500,3) = 6.433

BROUND(6.433503,3) = 6.434

BROUND(6.434500,3) = 6.435

BROUND(6.434503,3) = 6.434

Thank you so much for this article. It helped me tremendously!

in case of float, SQL round sometimes has problem, for example:

declare @x float

set @x=4.015

select ROUND(@x, 2) — 4.02 is expected but it returns 4.01

Any idea to make it return 4.02? except using:

select cast(ROUND(CAST(@x as decimal(38,10)),2) as float)

Thanks

Jeffrey Wang,

You could use the ceiling function instead, like this:

declare @x float

set @x=4.015

select @x,

ROUND(@x, 2), — 4.02 is expected but it returns 4.01

Ceiling(@x * 100)/100 — 4.02 is returned

Make sure to test this with negative numbers if you have any.