Rounding is a complicated topic. It is essentially changing the value of a number to an approximate defined by different rules. You could always 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. This article won’t look to define each one of them. It’s just going to show some basics.

Available Functions

The common option to round numbers is the ROUND() function, which uses the following syntax:

ROUND ( numeric_expression , length [ ,function ] )

The numeric expression is the number that we’re rounding. The length is the position to the right of the decimal point (negative values will count places to the left of the decimal point). The last argument allows us to choose between rounding and trunctating.

The default behavior for SQL Server is to round half away from zero as shown on Table 1.

Two other functions are available: CEILING() and FLOOR(). These functions have no length as they’ll only round up or down, respectively, to the closest integer. Note that when dealing with negative numbers, CEILING will continue to go away from zero. E.g. CEILING(-2.3) will return -2.

Here’s an example for basic rounding (the same sample data will be used on all the examples of this article) :

CREATE TABLE #Decimals(
    OriginalValue   decimal(10,4)
);
INSERT INTO #Decimals
VALUES
    (3.23),
    (3.76),
    (3.15),
    (3.5),
    (-2.34),
    (-2.89),
    (-2.25),
    (-2.5) ,
    (-2.2)
;
SELECT OriginalValue,
    ROUND(OriginalValue, 0) UsingRound,
    ROUND(OriginalValue, 0, 1) UsingRoundWithTruncate,
    FLOOR(OriginalValue)    UsingFloor,
    CEILING(OriginalValue)  UsingCeiling
FROM #Decimals;

With 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.500           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

Simulating CEILING and FLOOR with different lengths

CEILING() and FLOOR() have the important restriction to return just integers, but sometimes we need different lengths. Here’s an example on how to simulate the functionality for these functions with different lengths. I’m using a variable for the Length to make it easier to identify what needs to be changed on each formula to achieve different lengths. I’m also using a variable for 10 to define it as float and avoid truncation when using POWER().

DECLARE @Length int = -1,
    @10 float = 10;
SELECT OriginalValue,
    --1st Option
    FLOOR(  OriginalValue*POWER(@10,@Length))/POWER(@10,@Length)  SimulatingFloor,
    CEILING(OriginalValue*POWER(@10,@Length))/POWER(@10,@Length)  SimulatingCeiling,
    --2nd Option
    ROUND(OriginalValue-(.49*POWER(@10,-@Length)),@Length)    SimulatingFloor2,
    ROUND(OriginalValue+(.49*POWER(@10,-@Length)),@Length)  SimulatingCeiling2
FROM #Decimals;

Here are the results for the previous query:

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

Conditional rounding

In some cases, people need to round only if it would round towards zero. In that case, we just need to identify which numbers should be rounded and which shouldn’t. In other words, we need to identify which numbers would return the same output when rounded and truncated. Identifying that part, the rest just needed a CASE expression.

SELECT OriginalValue,
    CASE WHEN ROUND(OriginalValue, 0) = ROUND(OriginalValue, 0, 1)
            THEN ROUND(OriginalValue, 0)
            ELSE OriginalValue END
FROM #Decimals;

The results come like this:

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

Get the decimal part of a number

As mentioned at the beginning of this article, rounding is changing the value of a number. Sometimes, we would need to find the difference between these numbers, either to get the decimal part or evaluate how large the sum of these differences would be. This is achieved by subtracting the rounded value from the original value.

SELECT OriginalValue,
    OriginalValue - ROUND(OriginalValue, 0) RoundingDifference,
    OriginalValue - ROUND(OriginalValue, 0, 1) DecimalPart
FROM #Decimals;

Here’s the result from the previous query.

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

Cautions when rounding and aggregating

As mentioned, rounding can cause problems when aggregating the data. It’s especially noticeable when adding the values. Notice that you need to define if the sum needs to be precise or if it needs to be congruent with the values shown. Depending on the situation, be sure to define if rounding should be done before or after the aggregation as it can return different values. Even with our small set of values, we’re getting a difference.

SELECT SUM(OriginalValue) OriginalValueSUM,
    ROUND(SUM(OriginalValue), 0) RoundAfterAggregation,
    SUM(ROUND(OriginalValue, 0)) RoundBeforeAggregation
FROM #Decimals;

The results from this query are:

OriginalValueSUM    RoundAfterAggregation    RoundBeforeAggregation
------------------- ------------------------ ------------------------
1.460               1.000                    2.000

Conclusion

Lots of rules can be defined for rounding as there isn’t a definite definition on how it should be done. I hope that these examples can help you to solve problems you’ll face at work and be sure to share in the comments if you have solved a different problem.