# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « SQL Friday, The Best SQL Server Links Of The Past Week Episode 2 The Ten Most Asked SQL Server Questions And Their Answers »

# SQL Server Rounding Methods

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.

tsql
1. Create Function dbo.RoundUp(@Val Decimal(32,16), @Digits Int)
2. Returns Decimal(32,16)
3. AS
4. Begin
5.     Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
6.                 Then Ceiling(@Val * Power(10,@Digits))/Power(10, @Digits)
7.                 Else Round(@Val, @Digits)
8.                 End
9. 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.

tsql
1. Create Function dbo.RoundDown(@Val Decimal(32,16), @Digits Int)
2. Returns Decimal(32,16)
3. AS
4. Begin
5.     Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
6.                 Then Floor(@Val * Power(10,@Digits))/Power(10, @Digits)
7.                 Else Round(@Val, @Digits)
8.                 End
9. 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.

tsql
1. Create Function dbo.RoundToZero(@Val Decimal(32,16), @Digits Int)
2. Returns Decimal(32,16)
3. AS
4. Begin
5.     Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
6.                 Then Round(@Val, @Digits, 1)
7.                 Else Round(@Val, @Digits)
8.                 End
9. 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.

tsql
1. Create Function dbo.BankersRound(@Val Decimal(32,16), @Digits Int)
2. Returns Decimal(32,16)
3. AS
4. Begin
5.     Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
6.                 Then Round(@Val, @Digits, Case When Convert(int, Round(abs(@Val) * power(10,@Digits), 0, 1)) % 2 = 1 Then 0 Else 1 End)
7.                 Else Round(@Val, @Digits)
8.                 End
9. 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:

tsql
1. Create View vw_RandomBit
2. As
3. Select Case When Convert(Char(1), Convert(VarChar(36), NewId())) > '7'
4.             Then 1
5.             Else 0
6.             End As RandomBit

Then, we can create this function.

tsql
1. Create Function dbo.StochasticRound(@Val Decimal(32,16), @Digits Int)
2. Returns Decimal(32,16)
3. AS
4. Begin
5.     Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
6.                 Then Round(@Val, @Digits, Convert(int, (Select  RandomBit From vw_RandomBit)))
7.                 Else Round(@Val, @Digits)
8.                 End
9. 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.

39376 views

Comment from: northstar7 [Visitor]
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?
12/14/08 @ 09:27
Comment from: P. Ramesh [Visitor]
I was stuggling a bit to implement before seeing this code. Today, I am happy to note this code.
12/14/08 @ 22:31
Comment from: sangam [Visitor] · http://dotnetspidor.blogspot.com
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.
12/15/08 @ 05:50
Comment from: George Mastros (gmmastros) [Member]
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.

12/15/08 @ 05:57
Comment from: Joe Celko [Visitor] · http://www.celko.com
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.
12/15/08 @ 09:59
Comment from: hamzah [Visitor]
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

05/25/09 @ 01:45
Comment from: Tim [Visitor]
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.
11/24/09 @ 13:41
Comment from: The Mikeness [Visitor]

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.
03/09/10 @ 12:47
Comment from: The Mikeness [Visitor]
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.
03/09/10 @ 12:49
Comment from: The Mikeness [Visitor]
PLEASE DO NOT USE MY MYSQL FUNCTION ABOVE! IT IS WRONG!

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
01/28/11 @ 18:28
Comment from: Sera White [Visitor]
08/04/11 @ 13:04
Comment from: Jeffrey Wang [Visitor]
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
12/13/12 @ 14:43
Comment from: George Mastros (gmmastros) [Member]
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.
12/13/12 @ 15:08