In this article, we will delve into one of the mathematical functions in SQL Server, the ROUND function. The ROUND function in T-SQL allows us to round off numeric values to a specified length or precision.

The syntax for using the ROUND function is as follows:

ROUND(numeric_value, length)

The parameters for the ROUND function are:

• numeric_value: The number that needs to be rounded.
• length: The precision to which the value will be rounded. If a positive number is specified, the function will round after the decimal values. If a negative value is specified, the function will round before the decimal value.

Use Cases

We can categorize the use cases of the ROUND function into three segments:

1. Performing round after decimal values
2. Performing round before decimal values
3. Using the ROUND function with variables

Performing Round After Decimal Values

In this section, we will demonstrate how to round values after the decimal point. By specifying a positive number as the length argument, we can round the values accordingly.

Let’s consider an example:

SELECT ROUND(199.899344, 4),
       ROUND(199.899355, 4)

The first statement will return the output as 199.899300, indicating that the function has rounded the last two digits after the fourth digit post-decimal. In the second statement, the output is 199.899400. Here, the value has been rounded after the fourth digit post-decimal, but the third digit has changed from 3 to 4. This is because the ROUND function keeps the values the same if the first rounded value is less than 4, but increases the number when it is 5 or more.

Let’s consider another example:

SELECT ROUND(199.899344, 3),
       ROUND(199.899455, 3)

The output of the first statement is 199.899000. Here, only three digits are displayed (899), and the rest have been rounded to 0. The second statement returns the same output because the first rounded digit is less than 5.

We can further explore this by considering a number (199.899455) and specifying different lengths (0, 1, and 2) to observe the different results:

SELECT ROUND(199.899455, 2),
       ROUND(199.899455, 1),
       ROUND(199.899455, 0)

The first two statements return the same output as their first rounded value is more than 5. In this case, the function rounds the value up, resulting in 199.90000. The third statement returns 200.000000 because we have specified 0 as the length argument, so it rounds based on the first digit post-decimal value. As this value is more than 5, the value is rounded from 199.89XXXx to 200.000000.

Performing Round Before Decimal Values

To round values placed before the decimal number, we need to pass negative numbers as length arguments. Let’s consider an example:

SELECT ROUND(19889.9455, -3),
       ROUND(19889.9455, -2),
       ROUND(19889.9455, -1)

The output of these statements will be:

• 20000.0000 when -3 is specified as the length argument. As the third digit before the decimal is more than 5, 19xxx.xxxx is rounded to 20000.0000.
• 19900.0000 when -2 is specified as the length argument. As the second digit before the decimal is more than 5, 198xx.xxxx is rounded to 19900.0000.
• 19890.0000 when -1 is specified as the length argument. As the first digit before the decimal is more than 5, 1988x.xxxx is rounded to 19890.0000.

We can further explore this by considering a number with digits less than and greater than 5:

SELECT ROUND(14389.9455, -3),
       ROUND(14389.9455, -2),
       ROUND(14389.9455, -1)

The output of these statements will be:

• 14000.0000 when -3 is specified as the length argument. As the third digit before the decimal is less than 5, 14xxx.xxxx is rounded to 14000.0000. If the third digit were more than 5, 14000 would have become 15000.
• 14400.0000 when -2 is specified as the length argument. As the second digit before the decimal is more than 5, 143xx.xxxx is rounded to 14400.0000.
• 14390.0000 when -1 is specified as the length argument. As the first digit before the decimal is more than 5, 1438x.xxxx is rounded to 14390.0000.

Let’s consider one more example:

SELECT ROUND(14331.9455, -3),
       ROUND(14331.9455, -2),
       ROUND(14331.9455, -1)

In this example, none of the digits before the decimal are more than 5, so the rounded values remain the same.

Using the ROUND Function with Variables

We can also use the ROUND function with variables. Here’s an example:

DECLARE @round_value FLOAT
SET @round_value = -2;
SELECT ROUND(14389.9455, @round_value),
       ROUND(14331.9455, @round_value);

In this example, we have specified a variable as the length argument and passed it directly into the function. The output will be displayed accordingly.

We can also use variables for both arguments:

DECLARE @round_value FLOAT
DECLARE @input_number FLOAT
SET @round_value = 3
SET @input_number = 14331.94555
SELECT ROUND(@input_number, @round_value);

In this example, we have declared two variables, @round_value and @input_number, for both arguments. We then pass these variables into the ROUND function to obtain the result.

Conclusion

In this article, we have explored the ROUND function in SQL Server. The ROUND function is a useful tool for rounding off numeric values to a specified length or precision. We have discussed various use cases, including rounding after decimal values, rounding before decimal values, and using the ROUND function with variables. By understanding the functionality of the ROUND function, you can leverage it to fulfill your business requirements.

Please share your feedback in the comment section and let us know your thoughts about this function and when you have used it to fulfill your business requirements.