Hey guys!

In this article, I will share with you some tips and explanations about rounding numbers in SQL Server, using ROUND, FLOOR, CEILING and also using a custom function to meet the definitions of the ABNT NBR 5891 standard.

Introduction

Present in practically all information systems, rounding functions are widely used to deal with fractional and decimal numbers, especially with currency ($). Due to this importance, it is very important that the rounding rules are well understood so that there are no inconsistencies when rounding is applied to numbers, especially in large volumes of amounts.

To meet these needs, SQL Server provides us with 3 rounding functions:

  • FLOOR: Returns an integer, always rounded down, that is, it returns the integer part of the decimal number entered
  • CEILING: Returns an integer value, always rounding up, that is, the integer part + 1 of the decimal number entered (if the decimal value is > 0)
  • ROUND: Returns a decimal value, rounding according to the number of decimal places specified in the function. By default, if the decimal place is <= 4, it will be rounded down. If the decimal place is >= 5, it will be rounded up.

    This behavior can be changed by the 3rd parameter of the function, which, when the value 0 is entered, will truncate the data instead of rounding it, that is, 10.999 with 2 decimal places would be 10.99.

However, when we have a system that needs to follow the ABNT NBR 5891 standard, we have a problem, as the functions above do not meet the criteria of the standard, which is defined as follows:

Rounding rules

The rounding rules, following the ABNT NBR 5891 Standard, apply to decimal digits located in the position following the number of decimal digits that you want to transform, that is, if we have a number of 4, 5, 6, n decimal digits and we want to round to 2, these rounding rules will apply:

If the following decimal digits are less than 50, 500, 5000…, the previous one does not change.
If the following decimal digits are greater than 50, 500, 5000…, the previous one increases by one unit.
If the following decimal digits are equal to 50, 500, 5000…, the above applies; if it is even, the previous one does not change; if it is odd, the previous one increases by one.

Examples
When rounding to 2 decimal digits, we must take into account the third and fourth decimal. Thus, according to the previous rules:

The number 12.6529 would be rounded to 12.65 (here is 12.65, since 29 is less than 50, so it doesn't change)
The number 12.86512 would be rounded to 12.87 (here is 12.87, since 512 is greater than 500, so increase one unit)
The number 12.744623 would be rounded to 12.74 (here is 12.74, since 4623 is less than 5000, so it does not change)
The number 12.8752 would be rounded to 12.88 (here is 12.88, since 52 is greater than 50, so increase one unit)
The number 12.8150 would be rounded to 12.82 (here is 12.82, since the following digits are equal to 50 and the previous one is odd, in this case 1, then increase by one)
The number 12.8050 would be rounded to 12.80 (here is 12.80, since the next digits are equal to 50 and the previous one is even, in this case 0, so the previous one does not change)
The number 13.4666…, if we were to round to the whole part, it will always be rounded to 13, as 4666… will always be smaller than 5000… (If we round number by number, we would have: 13.4666… → 13.47 → 13.5 → 14, however, this would be saying that 13.4666… is closer to 14 than which is 13, which is not true. Therefore, we should not round the number that has already been rounded!!!)

Reference: https://pt.wikipedia.org/wiki/Arredondamento

Rounding numbers with FLOOR, ROUND and CEILING

As mentioned in the introduction to this article, SQL Server provides us with 3 functions for rounding:

  • FLOOR: Returns an integer, always rounded down, that is, it returns the integer part of the decimal number entered
  • CEILING: Returns an integer value, always rounding up, that is, the integer part + 1 of the decimal number entered (if the decimal value is > 0)
  • ROUND: Returns a decimal value, rounding according to the number of decimal places specified in the function. By default, if the decimal place is <= 4, it will be rounded down. If the decimal place is >= 5, it will be rounded up.

    This behavior can be changed by the 3rd parameter of the function, which, when the value 0 is entered, will truncate the data instead of rounding it, that is, 10.999 with 2 decimal places would be 10.99.

I will demonstrate some examples to make it easier to understand the difference between these functions:

SELECT
    CAST(CEILING(10.4925) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.4925) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(10.4925, 2) AS [Round]

SELECT
    CAST(CEILING(10.0001) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.0001) AS NUMERIC(18, 2)) AS [Floor],
    ROUND(10.0001, 2) AS [Round]

SELECT
    CAST(CEILING(10.5000) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.5000) AS NUMERIC(18, 2)) AS [Floor],
    ROUND(10.5000, 2) AS [Round]

In this example, we will use the 3rd parameter of the ROUND() function to force rounding up (0 = standard rounding, that is, 0 to 4 rounds down and 5 to 9 rounds up) and truncation (1 = Truncation, that is, it does not round, it simply cuts off the decimal places above the limit specified in the function).

SELECT
    CAST(CEILING(10.9999) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.9999) AS NUMERIC(18, 2)) AS [Floor],
    ROUND(10.9999, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(10.9999, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(10.9999, 2, 0)) AS Round_Up

SELECT
    CAST(CEILING(10.235) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.235) AS NUMERIC(18, 2)) AS [Floor],
    ROUND(10.235, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(10.235, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(10.235, 2, 0)) AS Round_Up

SELECT
    CAST(CEILING(10.225) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(10.225) AS NUMERIC(18, 2)) AS [Floor],
    ROUND(10.225, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(10.225, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(10.225, 2, 0)) AS Round_Up

As you can see, the ROUND() function meets 2 of the 3 rules defined in the ABNT standard, but in the example of the value 10,225, according to the standard, the rounding should have been done to 10.22 and not 10.23.

Rounding of numbers following the ABNT NBR 5891 standard

As we saw in the previous examples, when we fall into the rule “If the following decimal digits are equal to 50, 500, 5000…, the previous one is verified; if it is even, the previous one does not change; if it is odd, the previous one is increased by one unit.”, SQL Server's standard rounding functions end up not meeting this need.

To do this, I will provide the function below, which can fully comply with the rounding definitions of the ABNT NBR 5891 standard:

CREATE FUNCTION dbo.fncArredondamento_ABNT (
    @Valor NUMERIC(38, 16)
)
RETURNS NUMERIC(38, 16)
AS
BEGIN

    DECLARE
        @Parte_Inteira INT = ROUND(@Valor, 0, 1),
        @Parte_Decimal NUMERIC(38, 16) = @Valor - ROUND(@Valor, 0, 1),
        @Nova_Parte_Decimal NUMERIC(38, 16),
        @SegundoDecimal INT,
        @DoisPrimeirosDecimais AS NUMERIC(18, 2),
        @RestanteDosDecimais AS NUMERIC(38, 16)


    SELECT 
        @SegundoDecimal = SUBSTRING(CAST(@Parte_Decimal AS VARCHAR(40)), 4, 1),
        @DoisPrimeirosDecimais = '0.' + SUBSTRING(CAST(@Parte_Decimal AS VARCHAR(40)), 3, 2),
        @RestanteDosDecimais = '0.' + SUBSTRING(CAST(@Parte_Decimal AS VARCHAR(40)), 5, 16)
    
    
    SELECT
        @Nova_Parte_Decimal = (CASE
            WHEN @RestanteDosDecimais > 0.5 THEN @DoisPrimeirosDecimais + 0.01
            WHEN @RestanteDosDecimais < 0.5 THEN @DoisPrimeirosDecimais
            ELSE @DoisPrimeirosDecimais + IIF(@SegundoDecimal % 2 = 0, 0.00, 0.01)
        END)

    
    RETURN (@Parte_Inteira + @Nova_Parte_Decimal)


END

Usage examples

The number 12.6529 would be rounded to 12.65 (here is 12.65, since 29 is less than 50, so it does not change)

SELECT
    CAST(CEILING(12.6529) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.6529) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.6529, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.6529, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.6529, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.6529) AS [Round_ABNT]

The number 12.86512 would be rounded to 12.87 (here is 12.87, since 512 is greater than 500, so increase one unit)

SELECT
    CAST(CEILING(12.86512) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.86512) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.86512, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.86512, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.86512, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.86512) AS [Round_ABNT]

The number 12.744623 would be rounded to 12.74 (here is 12.74, since 4623 is less than 5000, so it does not change)

SELECT
    CAST(CEILING(12.744623) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.744623) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.744623, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.744623, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.744623, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.744623) AS [Round_ABNT]

The number 12.8752 would be rounded to 12.88 (here is 12.88, since 52 is greater than 50, so increase by one unit)

SELECT
    CAST(CEILING(12.8752) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.8752) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.8752, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.8752, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.8752, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.8752) AS [Round_ABNT]

The number 12.8150 would be rounded to 12.82 (here it is 12.82, since the following digits are equal to 50 and the previous one is odd, in this case 1, so one unit is increased)

SELECT
    CAST(CEILING(12.8150) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.8150) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.8150, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.8150, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.8150, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.8150) AS [Round_ABNT]

The number 12.8050 would be rounded to 12.80 (here is 12.80, since the following digits are equal to 50 and the previous one is even, in this case 0, so the previous one does not change)

SELECT
    CAST(CEILING(12.8050) AS NUMERIC(18, 2)) AS [Ceiling],
    CAST(FLOOR(12.8050) AS NUMERIC(18, 2))   AS [Floor],
    ROUND(12.8050, 2) AS [Round],
    CONVERT(DECIMAL(10,2), ROUND(12.8050, 2, 1)) AS Round_Down, 
    CONVERT(DECIMAL(10,2), ROUND(12.8050, 2, 0)) AS Round_Up,
    dbo.fncArredondamento_ABNT(12.8050) AS [Round_ABNT]

Well, that's it, folks!
I hope you enjoyed this post and see you next time!