Rounding Functions in SQL Server
September 24th 2008 06:37
Rounding Functions in SQL Server
Problem
I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
Solution
Rounding can become misunderstood if the underlying data types and rounding functions are not understood. Depending on the data type (integer, float, decimal, etc.) the rounded value can be different. In addition, depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation the values can differ as well. As such, it is important to find out the user rounding requirements then translate those requirements into the appropriate T-SQL command.
From a definition perspective, let's start here:
ROUND() - Rounds a positive or negative value to a specific length.
CEILING() - Returns the smallest integer greater than, or equal to, the specified numeric expression.
FLOOR() - Returns the largest integer less than or equal to the specified numeric expression.
Let's walk through each function with a few different data types to see the results.
ROUND(), CEILING() and FLOOR() Examples
In this example you can see with a positive integer all three rounding functions return the same value.
Example
DECLARE @value int
SET @value = 6
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
6
6
6
In the second example even with a negative integer all three rounding functions return the same value.
Example
DECLARE @value int
SET @value = -11
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-11
-11
-11
To prove the point with an integer, rounding is not possible. Let's check out some other data types.
Example
DECLARE @value int
SET @value = -11.5
SELECT ROUND(@value, 2)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-11
-11
-11
With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example. The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value. In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.
Example
DECLARE @value decimal(10,2)
SET @value = 11.05
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
11.10
11.05
11.05
12
11
As is the case with the example above, the 6 in the second digit to the right of the decimal place is significant based on the various length parameters.
Example
DECLARE @value decimal(10,2)
SET @value = -14.46
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-14.50
-14.46
-14.46
-14
-15
This example helps to illustrate the break down of rounded values across. This example also proves the point that the CEILING and FLOOR functions round to the nearest integer.
Example
DECLARE @value decimal(10,10)
SET @value = .5432167890
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT ROUND(@value, 4)
SELECT ROUND(@value, 5)
SELECT ROUND(@value, 6)
SELECT ROUND(@value, 7)
SELECT ROUND(@value, 8)
SELECT ROUND(@value, 9)
SELECT ROUND(@value, 10)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
0.5000000000
0.5400000000
0.5430000000
0.5432000000
0.5432200000
0.5432170000
0.5432168000
0.5432167900
0.5432167890
0.5432167890
1
0
In the final example, with a float data type you can see the same type of behavior as was the case with the decimal example above. In addition, the CEILING and FLOOR functions round to the nearest integer.
Example
DECLARE @value float(10)
SET @value = .1234567890
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT ROUND(@value, 4)
SELECT ROUND(@value, 5)
SELECT ROUND(@value, 6)
SELECT ROUND(@value, 7)
SELECT ROUND(@value, 8)
SELECT ROUND(@value, 9)
SELECT ROUND(@value, 10)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
0.1
0.12
0.123
0.1235
0.12346
0.123457
0.1234568
0.12345679
0.123456791
0.123456791
1
0
Problem
I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
Solution
Rounding can become misunderstood if the underlying data types and rounding functions are not understood. Depending on the data type (integer, float, decimal, etc.) the rounded value can be different. In addition, depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation the values can differ as well. As such, it is important to find out the user rounding requirements then translate those requirements into the appropriate T-SQL command.
From a definition perspective, let's start here:
ROUND() - Rounds a positive or negative value to a specific length.
CEILING() - Returns the smallest integer greater than, or equal to, the specified numeric expression.
FLOOR() - Returns the largest integer less than or equal to the specified numeric expression.
Let's walk through each function with a few different data types to see the results.
ROUND(), CEILING() and FLOOR() Examples
In this example you can see with a positive integer all three rounding functions return the same value.
Example
DECLARE @value int
SET @value = 6
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
value
6
6
6
In the second example even with a negative integer all three rounding functions return the same value.
Example
DECLARE @value int
SET @value = -11
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-11
-11
-11
To prove the point with an integer, rounding is not possible. Let's check out some other data types.
Example
DECLARE @value int
SET @value = -11.5
SELECT ROUND(@value, 2)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-11
-11
-11
With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example. The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value. In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.
Example
DECLARE @value decimal(10,2)
SET @value = 11.05
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
11.10
11.05
11.05
12
11
As is the case with the example above, the 6 in the second digit to the right of the decimal place is significant based on the various length parameters.
Example
DECLARE @value decimal(10,2)
SET @value = -14.46
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
-14.50
-14.46
-14.46
-14
-15
This example helps to illustrate the break down of rounded values across. This example also proves the point that the CEILING and FLOOR functions round to the nearest integer.
Example
DECLARE @value decimal(10,10)
SET @value = .5432167890
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT ROUND(@value, 4)
SELECT ROUND(@value, 5)
SELECT ROUND(@value, 6)
SELECT ROUND(@value, 7)
SELECT ROUND(@value, 8)
SELECT ROUND(@value, 9)
SELECT ROUND(@value, 10)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
0.5000000000
0.5400000000
0.5430000000
0.5432000000
0.5432200000
0.5432170000
0.5432168000
0.5432167900
0.5432167890
0.5432167890
1
0
In the final example, with a float data type you can see the same type of behavior as was the case with the decimal example above. In addition, the CEILING and FLOOR functions round to the nearest integer.
Example
DECLARE @value float(10)
SET @value = .1234567890
SELECT ROUND(@value, 1)
SELECT ROUND(@value, 2)
SELECT ROUND(@value, 3)
SELECT ROUND(@value, 4)
SELECT ROUND(@value, 5)
SELECT ROUND(@value, 6)
SELECT ROUND(@value, 7)
SELECT ROUND(@value, 8)
SELECT ROUND(@value, 9)
SELECT ROUND(@value, 10)
SELECT CEILING(@value)
SELECT FLOOR(@value)
value
0.1
0.12
0.123
0.1235
0.12346
0.123457
0.1234568
0.12345679
0.123456791
0.123456791
1
0
| 60 |
| Vote |
Add To: 
Comment by TimmyH
Tech News
Can you HACK it?
Genyration