Problem
SQL Server offers several techniques for converting decimal number values to integer number values. Please review the techniques in a way that helps me understand when to use each method for an assignment.
Solution
T-SQL includes at least three different built-in functions (round, ceiling, floor) that can convert a decimal value to an integer value. These built-in functions can also be used for other rounding tasks outside this tip's scope.
Jeremy Kadlec authored a prior tip (SQL Server Rounding Functions – Round, Ceiling and Floor) with clear examples of how to use the built-in functions for rounding to any level of precision. This tip briefly reviews the three built-in T-SQL functions for rounding a decimal value to an integer value before presenting a custom rounding approach based on the banker’s rounding rule. This rule is widely regarded as yielding a valid conversion of a decimal value to an integer value, but it is not implemented by any of the SQL Server functions. Each rounding technique can return different results. This tip closes with a section that offers a framework for comparing the four rounding techniques. All rounding examples in this tip are for transforming a positive decimal value to a positive integer. This tip provides the content you need for assessing the correct technique for any decimal-to-integer conversion assignment.
The T-SQL Round Function
The T-SQL round function is likely what many SQL professionals use to convert a decimal value to an integer value. The following expression for a round function converts a numeric expression to an integer:
round(numeric expression, 0)
The numeric expression can be a decimal data type with a total of p digits and s digits after the decimal point or an approximate numeric data type, such as a real or float data type.
Here is a set of examples for converting decimal values to integer values:
- The declare function specifies @decimal_value as a decimal data type with precision and scale settings of 19 and 4, respectively
- There are nine pairs of set and select statements
- The set statements assign values of 0.1 through 0.9 successively within each of the nine pairs of statements
- The select statements return the integer value by invoking the round function
- The current value of @decimal_value for each select indicates the decimal value to be converted to an integer
- The 0 after the @decimal_value term within each select statement designates the return of an integer value
-- prepared by Rick Dobson for MSSQLTips.com
-- syntax for rounding a decimal to an integer is round(number_to_round, 0)
-- the example converts decimal values of 0.1 through 0.9 to integer values
-- the function rounds to 0 four times for 0.1 through 0.4 decimal values and
-- rounds to 1 five times for 0.5 through 0.9 decimal values
declare @decimal_value decimal(19,4)
set @decimal_value = 0.1
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.2
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.3
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.4
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.5
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.6
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.7
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.8
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
set @decimal_value = 0.9
select @decimal_value [@decimal_value], round(@decimal_value,0) [round for @decimal_value]
Here are the nine results sets from the preceding script:
- When @decimal_value is from 0.1 through 0.4, the round function returns a value of 0; this is because the value of @decimal_value is less than 0.5 in each of these four statements
- When @decimal_value is from 0.5 through 0.9, the round function returns a value of 1; the round function rounds up when the digits after the decimal point designate a value that is equal to or greater than 0.5
Here is another set of examples that highlights the rounding behavior of the round function for decimal data type values when the fractional part of the value is .5. As you can see from the results set after the script, the round function rounds up for each decimal number value (0.5, 1.5, 2.5, and 3.5). This style of rounding for decimal values with a fractional part of 0.5 introduces a bias toward higher-rounded integer values. For example, the sum of the decimal values is 8.0, but the sum of the rounded integer values is 10. In this set of examples, the bias for the rounded integer values is two units greater than for the original decimal values.
-- prepared by Rick Dobson for MSSQLTips.com
-- the round function rounds up for 0.5, 1.5. 2.5, and 3.5
-- when the value for the fractional part of a decimal number is .5
-- the round function always rounds up
declare @decimal_value decimal(19,4)
set @decimal_value = 0.5
select @decimal_value [@decimal_value], round(@decimal_value,0) [ceiling of @decimal_value]
set @decimal_value = 1.5
select @decimal_value [@decimal_value], round(@decimal_value,0) [ceiling of @decimal_value]
set @decimal_value = 2.5
select @decimal_value [@decimal_value], round(@decimal_value,0) [ceiling of @decimal_value]
set @decimal_value = 3.5
select @decimal_value [@decimal_value], round(@decimal_value,0) [ceiling of @decimal_value]
The Ceiling and Floor Functions
The T-SQL ceiling and floor functions are two additional ways of converting decimal values to integer values within SQL Server. If you wanted to report the price of items to the nearest whole dollar, then either of these functions may be appropriate:
- When the ceiling function is applied to a positive decimal value, it returns the smallest integer greater than, or equal to, the decimal value
- When the floor function is applied to a positive decimal value, it returns the largest integer less than, or equal to, the decimal value
- When the decimal value to be converted has an integer value, then both the floor and ceiling functions return the same value (namely, the integer value for the decimal number)
Below is a script that shows three pairs of conversions from decimal values to integer values. Within each pair, the same decimal value (@decimal_value) is transformed to its integer equivalent:
- The first pair of conversions is for a decimal value of 16.9999
- The second pair of conversions is for a decimal value of 16.0001
- The third pair of conversions is for a decimal value of 16.0000
-- prepared by Rick Dobson for MSSQLTips.com
declare @decimal_value decimal(19,4)
set @decimal_value = 16.9999
select @decimal_value [@decimal_value], ceiling(@decimal_value) [ceiling of @decimal_value]
select @decimal_value [@decimal_value], floor(@decimal_value) [floor of @decimal_value]
set @decimal_value = 16.0001
select @decimal_value [@decimal_value], ceiling(@decimal_value) [ceiling of @decimal_value]
select @decimal_value [@decimal_value], floor(@decimal_value) [floor of @decimal_value]
set @decimal_value = 16.0000
select @decimal_value [@decimal_value], ceiling(@decimal_value) [ceiling of @decimal_value]
select @decimal_value [@decimal_value], floor(@decimal_value) [floor of @decimal_value]
Here are the results sets for the three pairs of conversions.
When the decimal value to be converted to an integer value is 16.9999
- The ceiling function returns a value of 17
- The floor function returns a value of 16
- The two converted values bound the original value being converted
When the decimal value to be converted to an integer value is 16.0001
- The ceiling function returns a value of 17
- The floor function returns a value of 16
- The two converted values bound the original value being converted
When the decimal value to be converted to an integer value is precisely 16.0000, the ceiling and floor functions return the same value of 16.
The Banker’s Rounding Custom Scaler Function
The fourth method in this tip for converting a decimal value to an integer is called banker’s rounding. In the context of this tip, banker’s rounding is significant because banker’s rounding implements a different style of rounding than the built-in SQL Server round functions.
Recall that the round function always rounds up when the fractional portion of a decimal value is 0.5. Some packages, such as the round function for Excel spreadsheets and the round function in SQL Server, implement by default this kind of rounding (called standard rounding). However, you can code a user-defined scalar function with T-SQL in SQL Server that implements banker’s rounding, which is a more accurate way of rounding decimal values. This section shows the function and demonstrates how to invoke the function.
For converting a decimal value to an integer value, the banker’s rounding rule depends on splitting the decimal value into two parts – an integer part for the digits to the left of the decimal point and a fractional part for the digits to the right of the decimal point. Then, the rule follows these three criteria for computing the converted integer value corresponding to a decimal value.
- When the integer part is even, and the fractional part is precisely 0.5, then the integer value for a decimal value corresponds to the integer part for the decimal value to be converted
- Next, when the fractional part of the decimal value is less than 0.5, then the integer value for a decimal value corresponds to the integer part for the decimal value to be converted
- Finally, if neither the first or second criteria are satisfied, then the integer value for a decimal value corresponds to the integer part for the decimal value plus 1
The following script shows a fresh version of the bankersround function in the dbo schema of the DataScience database. You can relocate the function to any schema within any database by changing the database name for the use statement and/or the schema name for the bankersround scalar function. The bankersround function below accepts an input parameter (@number) with a decimal (19,4) data type and returns the corresponding integer value.
- The conditional drop function statement after the use statement removes any prior version of the bankersround scalar function from the default database name in the dbo schema
- The create function statement initiates the creation of the bankersround function and specifies the input parameter name and data type (@number and decimal(19,4)) as well as the data type returned by the scalar function through its returns clause (int)
- After the as keyword, a begin…end block groups the statement executed by the scalar function
- These statements start with a declare statement to specify three local variables (@integerpart, @fractionalpart, and @bankersround)
- Then, two set statements compute the values for the @integerpart and @fractionalpart local variables
- Next, a select statement with a case…end statement computes a value for the @bankersround local variable. The case…end statement includes three when clauses with criteria that correspond to the three criteria for banker’s rounding listed in the preceding paragraph
- The return clause passes back the computed value for @bankersround as a scalar return value from the bankersround function
-- prepared by Rick Dobson for MSSQLTips.com
use DataScience
--go
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 invoking the bankersround function with a selection of different input parameters followed by the output from the script.
- The first three select statements invoke the bankersround function with input parameter values of 0.49, 0.5, and 0.51.
- When the decimal value is 0.49, then the function returns a value of 0 because the integer part of the decimal value is 0 and the fractional part of the decimal value is less than 0.5
- When the decimal value is 0.5, then the function also returns a value of 0 because the integer part of the decimal value is 0, and the first criterion for banker’s rounding is met
- When the decimal value is 0.51, then the function returns a value of 1, which is the value of the integer part of the decimal number plus 1
- The next four select statements display return values for successive odd (1 and 3) and even (2 and 4) decimal values with fractional values of 0.5. The displayed results confirm how the bankersround function operates for decimal values having an integer part that is either odd or even with a fractional part equal to 0.5.
- When the decimal value’s integer part is odd, and the fractional part of the decimal value equals 0.5, then the function returns a value equal to the integer part of the decimal value plus 1
- When the decimal value’s integer part is even, and the fractional part of the decimal value equals .5, then the function returns a value equal to the integer part of the decimal value
-- prepared by Rick Dobson for MSSQLTips.com
-- test runs of the bankersround function with different input parameters
select 0.49 number, (dbo.bankersround(0.49)) [number rounded to integer]
select 0.5 number, (dbo.bankersround(0.5)) [number rounded to integer]
select 0.51 number, (dbo.bankersround(0.51)) [number rounded to integer]
select 1.5 number, (dbo.bankersround(1.5)) [number rounded to integer]
select 2.5 number, (dbo.bankersround(2.5)) [number rounded to integer]
select 3.5 number, (dbo.bankersround(3.5)) [number rounded to integer]
select 4.5 number, (dbo.bankersround(4.5)) [number rounded to integer]
Comparing round, ceiling, floor, and bankersround Functions
The main objective of this tip is to compare the four methods reviewed for converting decimal values to integer values in a way that helps me to know when to use each technique.
The following script presents a simple T-SQL script for accomplishing this section’s objective. There are lots of other possible approaches, but all approaches should include the ability to list an original set of decimal values and the converted integer values resulting from each method being evaluated.
-- prepared by Rick Dobson for MSSQLTips.com
-- compare
-- SQL Server built-in round, ceiling, floor functions
-- and a custom dbo.bankesround function
-- for converting selected decimal values to integer values
use DataScience
go
declare @decimal_value decimal(19,4)
-- comparisons for before and after .5
set @decimal_value = 0.49
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 0.50
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 0.51
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
-- comparisons for odd and even decimal values
set @decimal_value = 1.5
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 2.5
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 3.5
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 4.5
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
-- comparisons for ceiling and floor decimal value examples
set @decimal_value = 16.9999
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 16.0001
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
set @decimal_value = 16.0000
select
@decimal_value [@decimal_value]
,round(@decimal_value,0) [round for @decimal_value]
,ceiling(@decimal_value) [ceiling for @decimal_value]
,floor(@decimal_value) [floor for @decimal_value]
,(select dbo.bankersround(@decimal_value)) [dbo.bankersround for @decimal_value]
Here is the output from the preceding script. Some subtle and not-so-subtle differences stand out to me. When you apply a modified version of this script to your own data, you or your client will likely discover other more important differences than those listed below.
- While the round function can return integer values, it displays the integer values in a decimal(19,4) data type. This is because the round function returns values in the data type of its input argument, which is decimal(19,4) for the examples above. If you want to see the converted integer values in an int format, then you can wrap the round function call in a convert function, such as convert(int,round(@decimal_value,0))
- The round function conversions are sometimes larger than the dbo.bankersround function conversions, but the dbo.bankersround function conversions are never larger than the round function conversions. This confirms that the round function sometimes rounds higher than the dbo.bankersround function. When this kind of difference is important to a project, then you may prefer the banker’s rounding because it can generate more accurate results
- On the other hand, you may discover that the round function returns values that correspond perfectly with a legacy approach to a task. If the importance of matching the legacy computations is more important than occasional errors from standard rounding, then you may opt to use the round function when deploying a solution
- If the decimal values represent item prices, and you want to exclude cents from your list of prices, then you may prefer either the ceiling or floor functions
- The floor function may be appropriate if your organization is under strong competitive pressure and you need a first draft of competitive prices to the nearest dollar
- The ceiling function may be appropriate if your organization is not under strong competitive pressure. The absence of strong competitive pressure may allow your organization to benefit from a first draft of less competitive prices to the nearest dollar
Next Steps
There are two broad next steps for this tip. First, verify that you can implement all four methods for rounding from a decimal value to an integer value with your own company’s data. You can repeat this first step for as many data types as you need to process. For example, some datasets may be for prices per item, others may be the number of visitors per branch office per shift, and others may be for the amount of oil recovered per well over successive months across years. Second, see which rounding technique for decimal values to integers yields the most reasonable results for your dataset type. This tip presents the code you need to tweak in both steps for your data.
