Problem
There is sometimes the need to round a dollar amount to the nearest penny and there are various ways this can be done with TSQL functions. In this article, we cover background issues on built-in and custom T-SQL rounding functions to customize rounding for application development projects.
Solution
Rounding is a mathematical technique that allows the expression of a number in a format that is more compact and easier to understand than the original numerical value. For example, if the average of a list of prices was $1.999, it might be easier to understand and remember if you denoted the average as $2.00. The original value of $1.999 may precisely represent the sum of the prices divided by the number of the prices, but it is not normally possible to buy a single item for $1.999. However, prospective buyers can purchase an item at the rounded average price of $2.00.
Many of us learned how to round numerical values as elementary school students. A common rule is to round up by one the least significant digit, such as the penny place, in the unrounded number when the digit to the right of the least significant digit is 5 or greater. Therefore, by this rule, because the last digit after the penny place is 9, the value $1.999 rounds to $2.00. Many other rounding rules can be applied.
Wikipedia offers an introductory overview of the purpose, types, and history of rounding. Another profound article on alternative rounding techniques appears in Rounding Algorithms Compared. These web-based articles offer informed general discussions of rounding, but their coverage is not specific to SQL Server.
Jeremy Kadlec authored a prior tip (SQL Server Rounding Functions – Round, Ceiling and Floor) with clear examples of using the built-in functions for rounding to any level of precision. Another MSSQLTips.com article (Comparing Ways to Round to the Nearest Integer with T-SQL) reviews three built-in T-SQL functions for rounding a decimal data type value to an integer data type value before presenting a custom rounding approach based on the banker’s rounding rule. The approach is custom because none of the built-in SQL Server functions implements banker’s rounding.
The banker’s rounding rule is widely regarded as yielding the most valid conversion of a decimal value to an integer value. For example, Peter Deegan, in a blog titled Why Excel’s Round() function is wrong, presents empirical evidence confirming that the banker’s rounding is more accurate relative to always rounding up when the digit after the least significant digit in the rounded number is 5 or greater; the example in the article is for the Excel worksheet round() function, but the SQL Server round() function performs identically to the Excel worksheet round() function.
The current tip drills down on different T-SQL script approaches for rounding monetary values to the nearest penny, including a couple of ways to implement the banker’s rounding rule for rounding to the nearest penny. This tip focuses on rounding decimal data type values that are positive.
The T-SQL Round() Function for Rounding to the Nearest Penny
The T-SQL round() function is probably what many SQL professionals use to convert a decimal value to its nearest penny value (after all, the function has the name round). The following expression for a round() function converts a decimal data type value. The least significant of the rounded decimal value digits is for pennies in the range of 0 through 9.
round(decimal_data_type_value, 2)
The decimal data type value to be rounded should have values for at least the thousandth place after the decimal point, such as decimal(19,3) or decimal(19,6). It is a good practice to represent monetary values with a decimal data type instead of a money data type (see this prior tip as well as this blog for details on the merits of this practice). The second parameter value in the preceding example for the round() function indicates that it rounds to the hundredth place. The round() function returns a rounded value in the data type of the input parameter value. For example, the first two places after the decimal point will have the rounded values. However, there can be additional trailing zeroes. If the decimal_data_type_value to be converted has a decimal(19,6) data type, the rounded value will have four zero values at the end of its returned value.
Here is a short script that illustrates the syntax for using the @round() function for nine decimal-type values.
- The code in the script starts with a declare statement for the @number_to_round local variable. The data type for the local variable is dec(19,4)
- The set statement immediately after the declare statement assigns a value of 123.43 to @number_to_round
- The initial set statement is followed by nine pair of set and select statements
- The set statement in the first pair adds .001 to the initial assignment value for @number_to_round
- The select statement in the first pair of statements displays the current value of @number_to_round followed by a rounded value to the nearest penny of @number_to_round. Aliases of [@number_to_round] and [rounded number] are assigned, respectively, to the first and second column values returned by the select statement
- The second through the ninth pair of set and select statements successively adds values of .001 to @number_to_round before displaying the local variable’s value from a select statement as both original and rounded values
-- prepared by Rick Dobson for MSSQLTips.com
-- Demonstrating the round () function
-- where the least significant digit of the number to be
-- rounded varies from 1 through 9
declare @number_to_round dec(19,4)
-- the script block
-- rounds down 4 times and
-- rounds up 5 times
-- the round() function has a built in bias for rounding up!
set @number_to_round = 123.43
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], round(@number_to_round,2) [rounded number]
Here is the output from the preceding script.
- The @number_to_round column value begins with 123.4310 and ends with 123.4390
- The first four rounded number column values are 123.4300; the @number_to_round column values for the first four rows have values of 1 through 4 in their thousandth place
- The last five rounded number column values are 123.4400; the @number_to_round column for the last five rows have values of 5 through 9 in their thousandth place
- All nine rows have a digit of 0 in their ten-thousandth place
The rounded number column values round down (or drop) the thousandth-place digit value for the first four rows. On the other hand, the rounded number column values round up the hundredth place digit value by .01 for the last five rows. If the @number_to_round column value had 0 in its thousandth place, then the rounded number column value would be 123.43 because 123.4300 equals 123.43.
If you carefully examine the results sets, you can see that the SQL Server round() function has a slight bias in favor of rounding up. This is because the round() function rounds down to 123.4300 four times, but it rounds up to 123.4400 five times. When it is essential that the rounded values are as close as possible to the original unrounded values, then you can benefit from applying another technique for rounding to the nearest penny.
A less important issue is that the values in the rounded number column end with 0 values in the thousandth and ten-thousandth places. Because @number_to_round has a dec(19,4) data type, so does the return value from the @round() function.
Converting the Data Type Returned by the Cast() Function
When you cast a decimal data type to a reduced scale value, SQL Server automatically rounds the value with the reduced scale. For example, if you have an original value with a dec(19,4) data type, and you cast the original value in the former decimal data type setting to a new value with dec(19,2) data type, then the new value is automatically rounded to two places after the decimal point. Furthermore, the rounding is to the nearest penny. The advantage of this casting and rounding in a single step causes the new values to display to the least significant digit. When the new values are displayed by a select statement, these values appear without trailing zeros for the decimal setting for the original value.
Here is a short script that illustrates the use of the cast function to both round a decimal data type value and recast the scale value of the decimal data type setting.
- As with the script in the preceding section, the code starts by declaring a local variable named @number_to_round to a dec(19,4) data type
- Similarly, the second line of code uses a set statement to assign 123.43 to @number_to_round
- There are nine pairs of set and select statements after the preceding set statement
- The set statement in each of these pairs adds .001 to the current value of @number_to_round
- The select statement in each pair has two column values
- The first column value is for the current value of @number_to_round
- The second column value is for the recast value of @number_to_round from a dec(19,4) data type to a dec(19,2) data type
- When the second column value is recast with a reduced scale setting of 2, instead of the scale setting of 4 for the most recent @number_to_round value, the new value concurrently rounds its value to the hundredths place
-- prepared by Rick Dobson for MSSQLTips.com
-- Demonstrating the cast () function for displaying
-- a reduced fixed number of digits after the decimal point
declare @number_to_round dec(19,4)
-- the script block
-- rounds down 4 times and
-- rounds up 5 times
-- the round() function has a built in bias for rounding up!
set @number_to_round = 123.43
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number] --round(@number_to_round,2)
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
set @number_to_round = @number_to_round + .001
select @number_to_round [@number_to_round], cast(@number_to_round as dec(19,2)) [rounded number]
Here is the output from the preceding script. Notice there are nine rows just as in the screenshot from the preceding section.
- Again, there are four rows in which the rounded number column value rounds down from its corresponding @number_to_round column value, and five rows in which the rounded number column value rounds up from its corresponding @number_to_round column value. This pattern indicates the same bias to round up slightly more than to round down. Furthermore, the bias is systematic, just as in the preceding section
- The number of digits after the decimal point in the rounded number column is always two. This is because the value for this column is cast as a dec(19,2) data type for all rows. Some readers and/or clients of an application may interpret this as an improvement in appearance relative to the screenshot in the preceding section. In any event, the choice of whether to display rounded number column values with two or four digits after the decimal point can be determined by you and/or the preferences of your application’s users
A Banker’s Rounding Script for Rounding to the Nearest Penny
This section will illustrate some custom code for rounding a dec(19,6) data type value with six places after the decimal point to a value with just two places after the decimal point. The code is meant for a demonstration of the possible rounding outcomes.
- When the last four digits after the decimal point divided by 10000 in the unrounded dec(19,6) value are less than .5, then the code rounds down by excluding these four digits in the rounded value. This is for standard arithmetic rounding as implemented by the SQL round() function
- When the last four digits after the decimal point divided by 10000 in the unrounded dec(19,6) value are greater than .5, then the code rounds up by a penny and excludes these four digits after the decimal point. Again, this is for standard arithmetic rounding as is implemented by the SQL round() function
- The second two outcomes illustrate banker’s rounding rules for converting a dec(19,6) data type value to a dec(19,2) data type value
- When the last four digits after the decimal point divided by 10000 in the unrounded dec(19,6) value are exactly equal to .5 and the first two digits after the decimal point are odd, then the code rounds up by excluding the last four digits and adding a penny when converting to the rounded dec(19,2) data type value
- When the last four digits after the decimal point divided by 10000 in the unrounded dec(19,6) value are exactly equal to .5 and the first two digits after the decimal point are even, then the code rounds down by excluding the last four digits when converting the unrounded dec(19,6) data type value in the rounded dec(19,2) data type value
Notice that banker’s rounding rules only apply when the last four digits after the decimal point divided by 10000 exactly equals .5. When the last four digits after the decimal point divided by 10000 is either greater than or less than .5, then standard arithmetic rounding rules, such as those implemented by the SQL round() function apply.
Here is a description of the unit test script for implementing the preceding rules in SQL code.
- This script can round one of four values: 123.454999, 123.445000, 123.455000, or 123.455001
- The code rounds two of the four dec(19,6) data type values (123.445000 or 123.455000) with banker’s rounding rules because banker’s rounding rules apply to them
- The other two dec(19,6) data type values (123.454999 or 123.455000) are rounded with arithmetic rounding rules because banker’s rounding rules do not apply to them
- The SQL code in the script starts with a declare statement for three local variables
- @dec_value has a dec(19,6) data type; the value of this local variable is the unrounded value with six places after the decimal point
- @str_dec_value has varchar(10) data type string characters corresponding to the digits and decimal point in @dec_value
- @penny_adjustment has a dec(19,2) data type. Subsequent code in the script sets this value to 0.00 when rounding down or to 0.01 when rounding up
- The next block of code is for assigning values to and displaying values for @dec_value and @str_dec_value
- There are four set statements for @dec_value. In any run of the script, only one of the four set statements should be uncommented. The uncommented set statement is the one that assigns a value to @dec_value; the version of the script below has the set statement for the 123.455000 dec(19,6) data value uncommented
- The last set statement in the code block converts the current value of @dec_value to a varchar(10) data type value with a cast() function
- This code block ends with a select statement to display the current values of @dec_value and @str_dec_value
- Then, a select statement displays the length of different parts of the value in @str_dec_value
- Next, a select statement parses and displays the key character strings in @str_dec_value and/or performs calculations on a parsed value
- Three strings parsed from @str_dec_value have column names of [digits before decimal point], [first two digits after decimal point], and [last four digits in @dec_value]
- The fourth column divides the numerical value of the [last four digits in @dec_value] by 10000
- The next select statement demonstrates the syntax for assessing if the first two places after the decimal value is odd (1) or even (0)
- The next code block, consisting of if and else if statements, assigns a value to @penny_adjustment. The assigned value is used by the script to determine if the script rounds up or down
- A value of 0.01 causes the script to round up
- A value of 0.00 causes the script to round down
- The last code block adds three components to display the rounded value. These components are
- The digits before the decimal point — the first part
- The first two original digits after the decimal point – the second part
- The current value of @penny_adjustment
-- prepared by Rick Dobson for MSSQLTips.com
-- unit test code for
-- rounding to the nearest cent for dec(19,6) data type value,
-- such as
-- 123.454999 rounds to 123.45
-- 123.445000 rounds to 123.44 -- banker's rounding
-- 123.455000 rounds to 123.46 -- banker's rounding
-- 123.455001 rounds to 123.46
-- declare @dec_value and @str_dec_value
declare
@dec_value dec(19,6)
,@str_dec_value varchar(10)
,@penny_adustment dec(19,2)
-- populate @dec_value with one of four values decimal data type values
-- and @str_dec_value with a corresponding varchar(10) data type value
--set @dec_value = cast(123.454999 as dec (19,6))
--set @dec_value = cast(123.445000 as dec (19,6))
set @dec_value = cast(123.455000 as dec (19,6))
--set @dec_value = cast(123.455001 as dec (19,6))
set @str_dec_value = cast(@dec_value as varchar(10))
-- display @dec_value and @str_dec_value
select @dec_value [@dec_value], @str_dec_value [@str_dec_value]
-- length, precision, decimal point position,
-- number of digits before and after decimal point
select
len(@str_dec_value) [length of @str_dec_value]
,len(@str_dec_value) - 1 [precision]
,charindex('.',@str_dec_value) [decimal point position]
,charindex('.',@str_dec_value) - 1 [number of digits before decimal point]
,len(@str_dec_value) - charindex('.',@str_dec_value) [number of digits after decimal point]
-- parse parts of @str_dec_value
select
substring(@str_dec_value,1, charindex('.',@str_dec_value) - 1) [digits before decimal point]
,substring(@str_dec_value,charindex('.',@str_dec_value) + 1,2)
[first two digits after decimal point]
,substring(@str_dec_value,7,4) [last four digits in @dec_value]
,cast(substring(@str_dec_value,7,4) as float)/10000 [last four digits divided by 10000]
-- 0 is for even original penny value
-- 1 is for odd original penny value
select substring(@str_dec_value,charindex('.',@str_dec_value) + 1,2) % 2 [odd/even penny value]
-- assign @penny_adjustment based on last four digits divided by 10000
-- and odd/even original penny value
if cast(substring(@str_dec_value,7,4) as float)/10000 < .5
set @penny_adustment = .00;
else if (cast(substring(@str_dec_value,7,4) as float)/10000) > .5
set @penny_adustment = .01;
else if ((select substring(@str_dec_value,charindex('.',@str_dec_value) + 1,2) % 2) = 1)
set @penny_adustment = .01;
else if ((select substring(@str_dec_value,charindex('.',@str_dec_value) + 1,2) % 2) = 0)
set @penny_adustment = .00;
-- display @penny_adustment
select @penny_adustment [@penny_adustment]
-- computed rounded value from parsed parts of @str_dec_value
-- plus @penny_adustment
select
cast(substring(@str_dec_value,1, charindex('.',@str_dec_value) - 1) as float) + -- first parsed part
cast(substring(@str_dec_value,charindex('.',@str_dec_value) + 1,2)as float)/100 + -- second parsed part
@penny_adustment [rounded @dec_value]
There are four possible results sets from the preceding script. Each result set is for a different value of @dec_value.
Here is the results set for a @dec_value of 123.454999.
Here is the results set for a @dec_value of 123.445000.
Here is the results set for a @dec_value of 123.455000.
Here is the results set for a @dec_value of 123.455001.
The first and the fourth results sets are for arithmetic rounding for @dec_values of 123.454999 and 123.455001, respectively. Notice that the first results set shows rounding down, and the fourth results set shows rounding up. These rounding actions follow the rules of arithmetic rounding.
The second and third results sets are for banker’s rounding for @dec_values of 123.445000 and 123.455000, respectively. Notice that the second results set shows rounding down, and the third results set shows rounding up. These rounding actions follow the rules for banker’s rounding.
Rounding to the Nearest Penny based on Rounding to the Nearest Integer
A prior tip (Comparing Ways to Round to the Nearest Integer with T-SQL) developed, explained, and demonstrated the use of a user-defined function to round to the nearest integer with banker’s rounding rules. It is also possible to adapt the user-defined function for rounding to the nearest integer so that it rounds to the nearest penny.
- All a script needs to do is convert a monetary value from dollar to penny units before invoking the user-defined function. You can do this by multiplying the monetary value by 100. This moves the decimal point for the value to be rounded from right after the dollar amount to right after the penny amount
- Then, you can submit the new monetary value (where pennies are integers) to the user-defined function for rounding to the nearest integer
- After the user-defined function returns a rounded value, you can divide that value by 100. This moves the decimal point for the rounded value from right after the pennies place to right after the dollars place
For your convenience, here is a script with the T-SQL code for rounding to the nearest integer.
- The use statement causes the function to be created in the DataScience database, but you can modify this to any other database that is convenient for your application
- The function’s name is dbo.bankersround
- The function is hard coded to accept a value with up to 4 places after the decimal point, and the function returns a rounded integer value
-- prepared by Rick Dobson for MSSQLTips.com
use DataScience
drop function if exists dbo.bankersround
go
create function dbo.bankersround (@number decimal(19, 4))
returns int
as
begin
declare @integerpart int, @fractionalpart decimal(19, 4), @bankersround int
set @integerpart = convert(int, @number)
set @fractionalpart = @number - @integerpart
select @bankersround = case
when @integerpart % 2 = 0 and @fractionalpart = .5000 then @integerpart
when @fractionalpart < .5000 then @integerpart
else @integerpart + 1
end
return @bankersround
end
Here is a short script for rounding a couple of monetary values to the nearest penny using the dbo.bankersround function.
- There are two examples of rounding to the nearest penny in the script
- The first one is for a monetary value of 123.455000; it is cast as a dec(19.6) data type value
- The second one is for a monetary value of 123.445000; it is cast as a dec(19.6) data type value
- The digits for cents in the first example, ignoring the decimal point, are 455000; this value rounds up because the last whole penny value is odd: 5 in 45. The value after the last whole penny is precisely half a penny (.005000)
- The digits for cents in the second example are 445000; this value rounds down because the last whole penny value is even (4) in 44. The value after the last whole penny is precisely half a penny (.005000)
-- prepared by Rick Dobson for MSSQLTips.com
use DataScience
-- based on round to the nearest integer
declare @number dec(19,6) -- for number to be rounded
-- assign a value to @number [number to be rounded]
set @number = cast(123.455000 as dec (19,6))
-- display number to be rounded in dollars
-- and number to be rounded in pennies
select
@number [number to be rounded in dollars]
,(@number)*100. [number to be rounded in pennies]
,cast(((dbo.bankersround(@number*100.)))/100. as dec(19,2))
[number rounded after moving decimal point]
--------------------------------------------------------------
-- assign a value to @number [number to be rounded]
set @number = cast(123.445000 as dec (19,6))
-- display number to be rounded in dollars
-- and number to be rounded in pennies
select
@number [number to be rounded in dollars]
,(@number)*100. [number to be rounded in pennies]
,cast(((dbo.bankersround(@number*100.)))/100. as dec(19,2))
[number rounded after moving decimal point]
Here are the results sets from the preceding script. The results reflect banker’s rounding rules:
- The top results set shows 123.455000 rounding to 123.46
- The bottom results set shows 123.445000 rounding to 123.44
Next Steps
This tip is about rounding monetary values to the nearest penny. It shows several different SQL Server approaches to the task. Each approach is demonstrated with sample data and T-SQL code. Also, the advantages of the approaches are indicated.
- The SQL Server @round function has a built-in bias to rounding up, but its method of rounding is nevertheless widely used
- Banker’s rounding does not have a built-in bias to rounding up, but there is no built-in SQL Server function to implement banker’s rounding
- Both arithmetic and banker’s rounding only approximate an original unrounded value with a rounded value; rounded values are necessarily not equal to their corresponding unrounded original values. However, the approximate rounded value from banker’s rounding is closer on average than for arithmetic rounding implemented by the SQL Server round() function. Also, you are reminded that over very large datasets, pennies can accumulate to significantly large values
- Recall that this tip offers two different SQL Server approaches for implementing banker’s rounding
- If you or the team to which you belong decide that your custom applications require the precision of banker’s rounding, adapt whichever method you prefer to implement from this tip
