In this article, I will explore one of the SQL Server mathematical functions, ROUND. The T-SQL ROUND() function rounds off a numeric value to a specified length or precision. The syntax to use this function is given below:

ROUND ( numeric_value , length )

The parameters are:

• Numeric_value is the number for which we need to get a rounded value.
• Length is the precision to which the value will be rounded. If you specify a positive number, the function will round after decimal values, and if you specify a negative value, the function will round before the decimal value.

Use Cases

I have divided its use cases into 3 segments, as given below.

• Perform round after decimal values
• Perform round before decimal values
• Use the ROUND function with variables

I will explain this T-SQL function for values given after decimal and how to round them with the help of this function. Similarly, the second segment will explain how to round the values specified before the decimal, and finally, I will showcase a few use cases of using this function with variables. Let’s start with the first segment below.

Perform round after decimal values

In this section, I will show you how to round values after the decimal. After specifying the length, we will specify a positive number, as a length argument, to round values. You can launch SQL Server management studio or SQL tools to connect to your SQL Server instance where you can practice these use cases.

Let’s take an example: I will use a random numeric value for which I will run the ROUND function to get its rounded value based on a specified length. Here I have taken two numeric values; one is 199.899344, and another one is 199.899355. Next, I have specified length as value 4 for both numbers. Let’s analyze its output.

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

The first statement has returned output as 199.899300, meaning the function has rounded the last two digits after 4th digit post decimal values. Let’s read another output in which we have output as 199.899400. Here also value has been rounded after the 4th digit post decimal, but you will notice that the 3rd digit has been changed to 4, whereas it is the same as 3 in the first statement. This is because the SQL ROUND function keeps values the same if the first rounded value is less than 4 whereas it will increase the number when it will be 5 or more.

Let’s understand it more with another example where I have taken similar numbers with different lengths, which are 3 this time. It means numbers will be rounded after the 3^rd digit post-decimal.

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

The output of the first statement is showing as 199.899000. here we can see only 3 digits are showing, i.e., 899, and the rest have been rounded to 0. The second statement has also returned the same output because the first rounded digit is less than 5.

Here is another example where I have taken the number 199.899455 and specified length as 0,1 and 2 to display different results, which can make your understanding better.

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

Let’s read the output. The first two statements have given the same output as their first rounded value is more than 5. In this case the function has rounds the value up, and output has returned from 199.89XXXX and 199.8XXXXX to 199.90000. The third statement has returned as 200.000000 because we have specified there 0 as the length argument, so it will round based on the first digit post decimal value. This value is more than 5, so the value has rounded from 199.89XXXx to 200.000000.

Perform round before decimal values

We need to pass negative numbers as length arguments to perform a round of values placed before the decimal number. I have taken a random number, 19889.9455, along with some negative numbers as a length argument to demonstrate this use case.

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

The below image shows the output of the above query. We can see the output of all 3 statements of the above query where I have specified -3, -2, and -1 as length arguments. The output of these statements are:

• 20000.0000 when specified -3 as the length argument. As the 3^rd digit before decimal is more than 5 so 19xxx.xxxx has rounded to 20000.0000
• 19900.0000 when specified -2 as the length argument. As the 2^nd digit before decimal is more than 5 so 198xx.xxxx has rounded to 19900.0000
• 19890.0000 when specified -1 as the length argument. As 1^st digit before the decimal is more than 5 so 1988x.xxxx has rounded to 19890.0000

Similarly, you can see another example where I specified a number with numbers less than and greater than 5 to see the rounded value.

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

The output of the above statements is:

• 14000.0000 when specified -3 as the length argument. As the 3^rd digit before the decimal is less than 5, so 14xxx.xxxx has rounded to 14000.0000. If the 3^rd digit is more than 5 then 14000 would have become 15000.
• 14400.0000 when specified -2 as the length argument. As the 2^nd digit before decimal is more than 5, so 143xx.xxxx has rounded to 14400.0000
• 14390.0000 when specified -1 as the length argument. As 1^st digit before the decimal is more than 5, so 1438x.xxxx has rounded to 14390.0000

You can have a look at one more example to understand it more. Here none of the digits before decimal are more than 5, so rounded values have not been increased to one number more and rounded to the same numbers.

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

Have a look at the output of the above query.

Use the ROUND function with variables

We can also use this system function with variables. You can see the example below in which I specified a variable as length argument and then passed just the variable in place length to get the result.

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

The output of the above query is given in the below image.

Similarly, we can also use variables for both arguments. Here, I have declared two variables for both arguments and then passed those variables into this system function to get the result.

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

We can analyze the result of the above query in the below image. I used a random number 14331.94555 and set this value to a variable @input_Number, another variable @round_value is declared for the length argument, and I set this value to 3. Next, I passed both variables in this function and got the result. We can see the rounded result as 14331.946 in the below image.

Conclusion

Today, in this article, I have explained one of the SQL system functions ROUND. 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 requirement.