When you are using the common rounding, you shouldn’t have any questions about it. After all, you do it on a daily basis and you don’t even acknowledge it. However, in what concerns to changing the value of a number to an approximate defined by different rules in an SQL server, things may get a bit complicated.
Discover everything you need to know about rounding up and down numbers.
The truth is that it is important to keep in mind that when you’re rounding numbers, you can round up or down, round half up or half down, towards zero or away from zero, or even to the nearest even or odd number.
SQL Server Rounding Tips And Tricks
Today, we decided to give you some SQL server rounding tips and tricks that can help you round numbers in this environment. So, let’s get started.
#1: Available Functions:
When you are trying to round numbers in an SQL server, the most common function used is the ROUND() function:
ROUND ( numeric_expression , length [ ,FUNCTION ] )
Simply put, this numeric expression is the number you’re rounding. It’s important to notice that the length is the position to the right of the decimal point. In what concerns the last argument, it allows you to choose between rounding and trunctating.
Discover what a rounding error is.
According to the default behavior of an SQL server, it will round half away from zero as you can see here:
| Original Value | Resultant value when rounding to integer |
| 3.2 | 3 |
| 3.7 | 4 |
| 3.5 | 4 |
| -2.3 | -2 |
| -2.8 | -3 |
| -2.5 | -3 |
But this isn’t the only function that you can use to round numbers in an SQL server. You can also use CEILING() and FLOOR(). These functions have no length as they’ll only round up or down, respectively, to the closest integer.
Notice that when you’re dealing with negative numbers, CEILING will continue to go away from zero. For example, CEILING(-2.3) will return -2.
Here’s an example for basic rounding:
We get the following results:
| OriginalValue | UsingRound | UsingRoundWithTruncate | UsingFloor | UsingCeiling |
| 3.230 | 3.000 | 3.000 | 3 | 4 |
| 3.760 | 4.000 | 3.000 | 3 | 4 |
| 3.150 | 3.000 | 3.000 | 3 | 4 |
| 3.400 | 4.000 | 3.000 | 3 | 4 |
| -2.340 | -2.000 | -2.000 | -3 | -2 |
| -2.890 | -3.000 | -2.000 | -3 | -2 |
| -2.250 | -2.000 | -2.000 | -3 | -2 |
| -2.500 | -3.000 | -2.000 | -3 | -2 |
| -2.200 | -2.000 | -2.000 | -3 | -2 |
These are the consequences of rounding errors.
#2: Using CEILING And FLOOR With Different Lengths
One of the things that you need to keep in mind when you’re using the CEILING and FLOOR functions in an SQL server is that to return just integers, they may need different lengths.
Let’s say that you’re using a variable for the length. This will make it easier so you can identify what needs to be changed in each formula to get different lengths. In this simple example, we will also be using a variable for 10 to define it as float. This way, we will be avoiding truncation when using POWER().
And here are the results you should get:
| OriginalValue | SimulatingFloor | SimulatingCeiling | SimulatingFloor2 | SimulatingCeiling2 |
| 3.230 | 3.20000000000 | 3.30000000000 | 3.2 | 3.3 |
| 3.760 | 3.70000000000 | 3.80000000000 | 3.7 | 3.8 |
| 3.150 | 3.10000000000 | 3.20000000000 | 3.1 | 3.2 |
| 3.500 | 3.50000000000 | 3.50000000000 | 3.5 | 3.5 |
| -2.340 | -2.40000000000 | -2.30000000000 | -2.4 | -2.3 |
| -2.890 | -2.90000000000 | -2.80000000000 | -2.9 | -2.8 |
| -2.250 | -2.30000000000 | -2.20000000000 | -2.3 | -2.2 |
| -2.500 | -2.50000000000 | -2.50000000000 | -2.5 | -2.5 |
| -2.200 | -2.20000000000 | -2.20000000000 | -2.2 | -2.2 |
#3: Conditional Rounding:
Sometimes, you may find the need to round a number only if it would round towards zero.
So, in this case, you need to identify the numbers that should be rounded and the ones that shouldn’t. To do this, you need a CASE expression.
And these are the results that you get:
| OriginalValue | ConditionalRound |
| 3.230 | 3.000 |
| 3.760 | 3.760 |
| 3.150 | 3.000 |
| 3.500 | 3.500 |
| -2.340 | -2.000 |
| -2.890 | -2.890 |
| -2.250 | -2.000 |
| -2.500 | -2.500 |
| -2.200 | -2.000 |
Check out these Excel formula errors when you’re rounding numbers.
#4: Get The Decimal Part Of A Number:
As we already stated at the beginning, when you round a number you are changing its value. While this may be a minor or major change, it is still a change.
Sometimes, you may find the need to discover the difference between these numbers. So, in order to do this, you need to subtract the rounded value from the original value.
And these are the results you get:
| OriginalValue | RoundingDifference | DecimalPart |
| 3.230 | 0.230 | 0.230 |
| 3.760 | -0.240 | 0.760 |
| 3.150 | 0.150 | 0.150 |
| 3.500 | -0.500 | 0.500 |
| -2.340 | -0.340 | -0.340 |
| -2.890 | 0.110 | -0.890 |
| -2.250 | -0.250 | -0.250 |
| -2.500 | 0.500 | -0.500 |
| -2.200 | -0.200 | -0.200 |
