Padding, Rounding, Truncating and Removing Trailing Zeroes - Page 3

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