SQL Server Rounding Functions – Round, Ceiling and Floor
By: Jeremy Kadlec | Read Comments (17) | Related Tips: 1 | 2 | 3 | 4 | More > Functions – System
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.
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 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
- 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:
- 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:
Let’s walk through each function with a few different data types to see the results.
SQL Server ROUND, CEILING and FLOOR Examples for Integer Data Types
Example 1a – In this first 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 and check out the result commented out on the right of the function.
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.
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.
Example 1d – Let’s round a negative integer and see the impacts with the result commented out on the right of the function.
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.
Example 2b – Here is a quick example of using the numeric data type with the ROUND function. This follows much of the same behavior as the decimal data type.
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.
Last Update: 2017-01-31
Technically, there aren’t an “insufficient number of digits” from example 1b. When rounding to the nearest 100 (or 1,000), 6 is just closer to zero. Same thing in 1c; 444 is closer to zero than to 1,000 or 10,000.
Good tip and explanation. This is pretty logical overall, but sometimes you really need to stop and think it through. These examples are a great help with that.
Monday, August 12, 2013 – 5:00:56 PM – Scott Coleman
In answer to ClaudioRound’s question “why this rounding does not work” (for 172.765).
Subtracting 128 from this value drops the two leftmost bits, so it gains two more fractional bits resulting in 101100.11000011110101110000101000111101011100001010010. (The mantissa is always 53 bits long in a float.) This is about 44.76500000000000057, so even though it has the same fractional digits the value of “ROUND(44.765, 2)” is 44.77.
Another fun fact is that “ROUND(CAST(172.7650000000000160090000000000000099 AS FLOAT), 2)” returns 172.76, but if you add a trailing 0 then “ROUND(CAST(172.76500000000001600900000000000000990 AS FLOAT), 2)” returns 172.77. Don’t ask me why.
The moral of the story is that if you really care about exact fractional values then don’t use FLOAT or REAL. Even casting it to DECIMAL before ROUNDING may help.
Wednesday, June 12, 2013 – 1:56:53 PM – Dave