By: Jeremy Kadlec | Comments (22) | Related: 1 | 2 | 3 | 4 | > Functions System
Problem
I saw your recent tutorial on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my stored procedures and scripts. 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 for the mathematical functions, let's start here:
- ROUND - Rounds a positive or negative value to a specific length and accepts
three values:
- Value to round
- Positive or negative number
- This data type can be an int (tiny, small, big), decimal, numeric, money or smallmoney
- Precision when rounding
- Positive number rounds on the right side of the decimal point
- Negative number rounds on the left side of the decimal point
- Truncation of the value to round occurs when this value is not 0 or not included
- Value to round
- CEILING - Evaluates the value on the right side of the decimal and returns
the smallest integer greater than, or equal to, the specified numeric expression
and accepts one value:
- Value to round
- FLOOR - Evaluates the value on the right side of the decimal and returns
the largest integer less than or equal to the specified numeric expression and
accepts one value:
- Value to round
Let's walk through each function with a few different data types to understand the T-SQL syntax and see the results.
Microsoft SQL Server ROUND, CEILING and FLOOR Examples for Integer Data Types
Example 1a - In the following example let's just look at rounding a positive integer for the precision value of 1 yields all three rounding functions returning the same value. In this example we are using a variable with the functions called by a SELECT statement and check out the result commented out on the right of the function.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1) -- 6 - No rounding with no digits right of the decimal point SELECT CEILING(@value) -- 6 - Smallest integer value SELECT FLOOR(@value) -- 6 - Largest integer value
Example 1b - Since the CEILING AND FLOOR functions do not have any optional values, let's test some options with the ROUND function. In this example, let's see the impacts of a negative number as the precision as well as the specifying additional positions that exceed the value to round. Check out these results with the result commented out on the right of the function.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1) -- 6 - No rounding with no digits right of the decimal point SELECT ROUND(@value, -1) -- 10 - Rounding up with digits on the left of the decimal point SELECT ROUND(@value, 2) -- 6 - No rounding with no digits right of the decimal point SELECT ROUND(@value, -2) -- 0 - Insufficient number of digits SELECT ROUND(@value, 3) -- 6 - No rounding with no digits right of the decimal point SELECT ROUND(@value, -3) -- 0 - Insufficient number of digits
Example 1c - Let's expand the digits in this example with the ROUND function and see the impacts with the result commented out on the right of the function.
SELECT ROUND(444, 1) -- 444 - No rounding with no digits right of the decimal point SELECT ROUND(444, -1) -- 440 - Rounding down SELECT ROUND(444, 2) -- 444 - No rounding with no digits right of the decimal point SELECT ROUND(444, -2) -- 400 - Rounding down SELECT ROUND(444, 3) -- 444 - No rounding with no digits right of the decimal point SELECT ROUND(444, -3) -- 0 - Insufficient number of digits SELECT ROUND(444, 4) -- 444 - No rounding with no digits right of the decimal point SELECT ROUND(444, -4) -- 0 - Insufficient number of digits SELECT ROUND(555, 1) -- 555 - No rounding with no digits right of the decimal point SELECT ROUND(555, -1) -- 560 - Rounding up SELECT ROUND(555, 2) -- 555 - No rounding with no digits right of the decimal point SELECT ROUND(555, -2) -- 600 - Rounding up SELECT ROUND(555, 3) -- 555 - No rounding with no digits right of the decimal point SELECT ROUND(555, -3) -- 1000 - Rounding up SELECT ROUND(555, 4) -- 555 - No rounding with no digits right of the decimal point SELECT ROUND(555, -4) -- 0 - Insufficient number of digits SELECT ROUND(666, 1) -- 666 - No rounding with no digits right of the decimal point SELECT ROUND(666, -1) -- 670 - Rounding up SELECT ROUND(666, 2) -- 666 - No rounding with no digits right of the decimal point SELECT ROUND(666, -2) -- 700 - Rounding up SELECT ROUND(666, 3) -- 666 - No rounding with no digits right of the decimal point SELECT ROUND(666, -3) -- 1000 - Rounding up SELECT ROUND(666, 4) -- 666 - No rounding with no digits right of the decimal point SELECT ROUND(666, -4) -- 0 - Insufficient number of digits
Example 1d - Let's round a negative integer and see the impacts with the result commented out on the right of the function.
SELECT ROUND(-444, -1) -- -440 - Rounding down SELECT ROUND(-444, -2) -- -400 - Rounding down SELECT ROUND(-555, -1) -- -560 - Rounding up SELECT ROUND(-555, -2) -- -600 - Rounding up SELECT ROUND(-666, -1) -- -670 - Rounding up SELECT ROUND(-666, -2) -- -700 - Rounding up
Example 1e - In our last example of this section, do not get fooled by your data types and actual values. In this example, the @value parameter is declared as an INT, but the value passed looks more like a decimal. Under these circumstances, let's see how the values are evaluated.
DECLARE @value int SET @value = 16.999999 SELECT ROUND(@value, 1) -- 16 - No rounding with no digits right of the decimal point i.e. int SELECT ROUND(@value, -1) -- 20 - Round up SELECT CEILING(@value) -- 16 - Smallest integer value SELECT FLOOR(@value) -- 16 - Largest integer value SELECT @value -- 16 - Shows how the @value is evaluated based on the int data type
Microsoft SQL Server ROUND, CEILING and FLOOR Examples for Decimal, Numeric and Float Data Types
Example 2a - 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.
DECLARE @value decimal(10,2) SET @value = 11.05 SELECT ROUND(@value, 1) -- 11.10 SELECT ROUND(@value, -1) -- 10.00 SELECT ROUND(@value, 2) -- 11.05 SELECT ROUND(@value, -2) -- 0.00 SELECT ROUND(@value, 3) -- 11.05 SELECT ROUND(@value, -3) -- 0.00 SELECT CEILING(@value) -- 12 SELECT FLOOR(@value) -- 11 GO
Example 2b - Here is a quick example of using the numeric data type with the ROUND function. This follows much of the same behavior for the numeric value as the decimal value.
DECLARE @value numeric(10,10) SET @value = .5432167890 SELECT ROUND(@value, 1) -- 0.5000000000 SELECT ROUND(@value, 2) -- 0.5400000000 SELECT ROUND(@value, 3) -- 0.5430000000 SELECT ROUND(@value, 4) -- 0.5432000000 SELECT ROUND(@value, 5) -- 0.5432200000 SELECT ROUND(@value, 6) -- 0.5432170000 SELECT ROUND(@value, 7) -- 0.5432168000 SELECT ROUND(@value, 8) -- 0.5432167900 SELECT ROUND(@value, 9) -- 0.5432167890 SELECT ROUND(@value, 10) -- 0.5432167890 SELECT CEILING(@value) -- 1 SELECT FLOOR(@value) -- 0
Example 2c - In the final example, with a float data type you can see the same type of behavior as was the case with the decimal and numeric examples above with the ROUND, CEILING and FLOOR functions.
DECLARE @value float(10) SET @value = .1234567890 SELECT ROUND(@value, 1) -- 0.1 SELECT ROUND(@value, 2) -- 0.12 SELECT ROUND(@value, 3) -- 0.123 SELECT ROUND(@value, 4) -- 0.1235 SELECT ROUND(@value, 5) -- 0.12346 SELECT ROUND(@value, 6) -- 0.123457 SELECT ROUND(@value, 7) -- 0.1234568 SELECT ROUND(@value, 8) -- 0.12345679 SELECT ROUND(@value, 9) -- 0.123456791 SELECT ROUND(@value, 10) -- 0.123456791 SELECT CEILING(@value) -- 1 SELECT FLOOR(@value) -- 0
Next Steps
- Rounding values can cause confusion with various data types, so be sure to understand the user requirements then translate them to the correct data types and rounding functions.
- Check out these related tips on MSSQLTips:
- Calculating Mathematical Values in SQL Server
- SQL Server Text Data Manipulation
- Date/Time Conversions Using SQL Server
- SQL Server T-SQL Aggregate Functions
- Daylight Savings Time Functions in SQL Server
- SQL Server System Function Tips
- SQL Server User Defined Function Tips
- COALESCE SQL Function
- SQL Server SUBSTRING
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips