Numerical values are commonly used to represent information in databases. However, these values often have unnecessary decimal places that are not needed for data analysis, presentation, or visualization. In SQL Server, we can use the ROUND() function to round values to a specified number of decimal places or the nearest integer.

Syntax of the ROUND() Function

The syntax of the ROUND() function in SQL Server is as follows:

ROUND(numeric_expression, length [, function])

The mandatory arguments for the ROUND() function are:

• numeric_expression: The number (input value) you want to round.
• length: The number of decimal places to round the numeric value. It can be a positive or negative integer.

The function also has an optional third argument that specifies the rounding method. The default behavior is to round the value (function = 0), but you can use a non-zero value to truncate the value.

Examples of Using the ROUND() Function

Let’s look at some examples to understand how the ROUND() function works:

Example 1: Rounding Numeric Values After Decimal Places

Suppose we have two numeric values: 4368.788444 and 4368.788851. We want to round these values to the 4th decimal place. We can use the ROUND() function as follows:

SELECT 
   ROUND(4368.788444, 4) as Round1,
   ROUND(4368.788851, 4) as Round2

The output of this query will be:

Round1: 4368.7884
Round2: 4368.7889

The ROUND() function looks at the first rounding digit (in this case, the 5th digit). If the digit is less than 5, the final value is rounded off to 4 decimal places. However, if the digit is greater than or equal to 5, the length digit (4th digit) is increased by one and the value is rounded.

Example 2: Rounding Numeric Values Before Decimal Places

In the previous examples, we used positive integers for the length argument to round the decimal places. However, we can also use negative integers to round the numeric value before the decimal point. Let’s look at a few SQL queries:

SELECT 
   ROUND(12345.19, -1) as Round1,
   ROUND(12345.14, -2) as Round2,
   ROUND(12345.14, -3) as Round3

The output of this query will be:

Round1: 12350.00
Round2: 12300.00
Round3: 12000.00

In the first example, we specified a length of -1, which rounds the number to the nearest 10. Since the digit to be rounded is greater than or equal to five, it is rounded to 12350.00. The remaining examples round off the left side of the decimal point value.

Example 3: Using Variables in the ROUND() Function

You can use variables to assign input values in the ROUND() function. In SQL Server, you declare a variable with the “@” symbol. Here is an example:

DECLARE @value DECIMAL(10, 2);
SET @value = 20.19;
SELECT 
   ROUND(@value, 1) as Round1,
   ROUND(@value, 2) as Round2,
   ROUND(@value, 3) as Round3

The output of this query will be:

Round1: 20.2
Round2: 20.19
Round3: 20.190

You can also use a float data type variable in the ROUND() function:

DECLARE @value FLOAT(10);
SET @value = 20.19;
SELECT 
   ROUND(@value, 1) as Round1,
   ROUND(@value, 2) as Round2,
   ROUND(@value, 3) as Round3

Example 4: Rounding and Truncating

The ROUND() function has an optional third argument that specifies the rounding method. Let’s look at an example:

SELECT    
   ROUND(230.75, 0) as Rounding,
   ROUND(230.75, 0, 1) as Truncating

The output of this query will be:

Rounding: 231.00
Truncating: 230.00

The first ROUND() function rounds the input value to the nearest integer. The second ROUND() function truncates the input value if the third argument is non-zero.

Avoiding Arithmetic Overflow Error

When using the ROUND() function with negative lengths, it is important to ensure that the length does not cause an arithmetic overflow error. Let’s look at an example:

SELECT 
   ROUND(919.15, -1) as Round1,
   ROUND(919.15, -2) as Round2,
   ROUND(919.15, -4) as Round4,
   ROUND(919.15, -5) as Round5

The output of this query will be:

Round1: 920.00
Round2: 900.00
Round4: 0.00
Round5: 0.00

If a negative length is used and the length is larger than the number of digits before the decimal point, the ROUND() function returns 0. However, if the length is too small, it can cause an arithmetic overflow error. To avoid this error, you can change the data type to the appropriate length using the CAST() function.

These are some examples of how to use the ROUND() function in SQL Server to round values in a database. By understanding the syntax and behavior of the ROUND() function, you can manipulate numerical values for data analysis, presentation, or visualization purposes.

Article Last Updated: 2023-06-14