Rounding Number to a Certain Number of Decimal Places
SQL
Server provides the ROUND function to perform rounding operations. When
rounding decimal numbers, there are three kinds of rounding that your
applications might need, round up, round down, or true rounding. My definitions
for these are as follows:
Rounding up means always rounding to
the next highest number;

Rounding down means always rounding
down to the next lowest number;

True rounding means rounding up to the
next highest number if the part that is to be truncated is 5 or greater, and
rounding down if the part that is to be truncated is less than 5. Round down
is also known as truncating.
The
ROUND function supports rounding down and true rounding, although it does not
support my definition of rounding up. To round up you will need to perform
some extra steps prior to using the ROUND function. I will build an example
for each of these rounding methods. The ROUND function has the following
syntax:
ROUND ( numeric_expression , length [ , function ] )
Where
the numeric_expression is the number you want to round, length is the number of
characters to leave while rounding to the right or left of the decimal point.
If the length is positive, then numbers are rounded to the right of the decimal
point, where as when the length is negative then numbers are rounded to the
left of the decimal point. The function parameter is optional. This parameter
determines whether the ROUND function will round or truncate the numeric_expression.
If the function parameter is a zero (0), the default, then rounding will occur;
any other value for this parameter will cause the ROUND function to truncate
the numeric_expression.
Let's
say you have a loan application. When you calculate the interest amount on a
given loan, you usually end up with a fractional portion of a cent. However,
you cannot charge a fraction of a cent, since there is no way for the customer
to pay a fraction of a cent. Therefore, instead you decide your application
should round the interest amount up, so you can collect a fraction of a cent
more on each interest payment made. Here is some code that would do just that:
declare @loan_amt decimal(10,2)
declare @interest_rate decimal(7,5)
declare @monthly_interest_amt decimal(10,4)
set @loan_amt = 123456.78
set @interest_rate = 6.75
select @monthly_interest_amt= (@loan_amt * (@interest_rate/100)) * 1/12
print @monthly_interest_amt
select @monthly_interest_amt = @monthly_interest_amt + .005
print @monthly_interest_amt
select cast(round (@monthly_interest_amt,2,0) as decimal(10,2))
As you
can see from this example, I calculated the monthly interest amount by using
the following formula (@loan_amt * (@interest_rate/100)) * 1/12. This
calculation produces a monthly interest amount of 694.4444. Now since the
there is no way to force the ROUND function to round up, I added .005 (B= cent)
to the calculated monthly interest amount. By doing this, the final displayed
monthly interest amount is 694.45.
Now say
you have a bank account type of application and you need to calculate the
amount of interest a particular account receives each month. Like the previous
example, you do not want to give an account a fraction of a cent, so your
interest calculation needs to truncate the factional cent value down to the
nearest penny. The ROUND function provides the round down function, by
providing a positive number for the function parameter. Here is an example
that shows how to round down, to truncate the fraction of a cent from the
calculated interest earned for a particular bank account balance.
declare @bank_account_bal decimal(10,2)
declare @interest_rate decimal(7,5)
declare @interest_earned decimal(10,4)
set @bank_account_bal = 2516.78
set @interest_rate = 4.99
select @interest_earned= (@bank_account_bal * (@interest_rate/100)) * 1/12
print @interest_earned
print @interest_earned
select round (@interest_earned,2,1)
select cast(round (@interest_earned,2,1) as decimal(10,2))
For the
last rounding example, I am going to show two different ways to do true
rounding. One method uses the ROUND function and the other method uses the
CAST function. Let's assume we have an application that calculates the average
number of transactions per second for the day. This application calculates the
average transaction per second, by taking the number of transactions for a day
and divides by the number of seconds in a day. Below you will find code that
does true rounding while calculating the average transaction per second for two
different days.
set nocount on
declare @num_of_trans decimal (15,0)
declare @avg_trans_per_sec decimal (15,8)
 Round up example for day 1
print 'Example 1'
set @num_of_trans = 123456
set @avg_trans_per_sec = @num_of_trans / (24*60*60)
select @avg_trans_per_sec,
round(@avg_trans_per_sec,2,0),
cast(round(@avg_trans_per_sec,2,0) as decimal (15,2)),
cast(@avg_trans_per_sec as decimal (15,2))
 Round down example for day 2
print 'Example 2'
set @num_of_trans = 123456789
set @avg_trans_per_sec = @num_of_trans / (24*60*60)
select @avg_trans_per_sec,
round(@avg_trans_per_sec,2),
cast(round(@avg_trans_per_sec,2) as decimal (15,2)),
cast(@avg_trans_per_sec as decimal (15,2))
The
output from this code looks like this:
Example 1
   
1.42888889 1.43000000 1.43 1.43
Example 2
   
1428.89802083 1428.90000000 1428.90 1428.90
As
you can see from the output of these two examples, the ROUND function and the CAST
perform true rounding. In example 1, I set the function parameter to 0 for the
call to the ROUND function, where as I took the default value for the function
parameter in the second example. Note that even though you specify to round to
two decimal places the round function does not remove the trailing zeroes. If
you want to use the ROUND function as well as remove the trailing zeroes, you
will also need to use the CAST statement to truncate the decimal number. Now
using the CAST function can perform the rounding and truncation while casting
the output, as the example demonstrates.
Padding with Zeroes Which One Performs Better
Ok, which one do you think performs better? From my testing, the
following example performs 24% faster then the next closest one.
select top 10 right('000' + convert(varchar(3),job_lvl), 3) as jl from employee
Just in case there are any questions about how I did my
performance testing, here is my performance benchmark code. I ran the following
code on a standalone machine. This code was run three times for each example
and I recorded the duration for each time. I then threw out the fastest and
slowest time, and associated the middle duration as the time it takes to
perform each example 100,000 times.
declare @j int
declare @I int
declare @s datetime
set @I = 0
set @s = getdate()
while @I < 100000
begin
select top 10 @j=right('000' + convert(varchar(3),job_lvl), 3) from employee
set @I = @I + 1
end
print cast(datediff(ms,@s,getdate()) as char)
Conclusion
Depending on your application requirements, you may need to remove
trailing zeroes, add leading zeroes and/or round decimal numbers. I have shown
you a number of techniques to do each of these. Also, remember there are many
ways to perform a given task. If you know of more than one way to accomplish
your task and performance is a consideration then run your code through a
performance test like the one I did above to determine which method is more
efficient.
»
See All Articles by Columnist Gregory A. Larsen