The SQL ROUND function rounds a numeric value up to a specified number of decimal places or precision.

Description

The SQL ROUND function helps to handle numeric data according to the requirements. Some other SQL functions for this purpose are CEILING, FLOOR, etc. If we do not want to display complete numeric values, we can truncate or round them using the ROUND function.

SQL ROUND Usage Notes

The first argument of the ROUND function is the numeric expression that we want to round. It can be of any numeric data type but not the bit data type.

The length is the second argument of tinyint, smallint, or int data type. The length argument can be positive and negative. For positive value, the numeric expression rounds to the number of decimal places specified by the length. For negative length value, the ROUND function rounds the numeric expression to the left side of the decimal point equal to the length value.

The third argument of the ROUND function is function. It specifies the operation to perform on the numeric expression. The numeric values round according to the specified length for the default value of 0. In the case of a length value other than 0, the numeric expression truncates. The data type of the function argument is tinyint, smallint, or int.

The ROUND function returns a numeric value. The return data type can be int, bigint, money, decimal, float according to the input data type. If the specified length is negative and longer than the number of digits in the numeric expression, the function returns 0.

Syntax

ROUND (numeric_expression, length, function (optional))

SQL ROUND Examples

We can use the ROUND function for estimations, approximations, and truncations. The following examples demonstrate using the ROUND function on a decimal type:

SELECT ROUND(920.9834, 3) result;
/* Answer */
SELECT ROUND(920.9834, 3) result;

The above query rounds the numeric value to 3 decimal places, and the output is 920.9830.

SELECT ROUND(920.9835, 3) result;
/* Answer */
SELECT ROUND(920.9835, 3) result;

Here, we get a different estimate due to the last decimal digit. The 5 on the last decimal place rounds the value to 920.9840.

The following query rounds on the left side of the decimal as the length is negative.

SELECT ROUND(920.9835, -2) result;
/* Answer */
SELECT ROUND(920.9835, -2) result;

The output is 900.000. But what if we change the second digit to 5?

SELECT ROUND(950.9835, -2) result;
/* Answer */
SELECT ROUND(950.9835, -2) result;

The result is an arithmetic overflow. The round function will try to increase 9 by 1, but we will not get 1000 as an output due to arithmetic overflow. To avoid overflow, we first convert the numeric expression to a bigger decimal value and then apply the ROUND function to it.

SELECT ROUND(CAST (950.9835 AS decimal (6,2)),-2) result;
/* Answer */
SELECT ROUND(CAST (950.9835 AS decimal (6,2)),-2) result;

In the following example, we will see how the operation argument affects the ROUND output.

SELECT ROUND(920.9834, 0, 1) result1; SELECT ROUND(920.9834, 2, 1) result2;
/* Answer */
SELECT ROUND(920.9834, 0, 1) result1;
SELECT ROUND(920.9834, 2, 1) result2; 

The first query truncates the numeric value to zero decimal places. The output is 920.0000. The second query truncates to 2 decimal places, and the output is 920.9800.

Lastly, we will see how to use the ROUND function on column values of a database. We will use the AdventureWorks2019 database for the following query.

SELECT ROUND(StandardCost,2) RoundedCost FROM Production.ProductCostHistory
/* Answer */
SELECT ROUND(StandardCost,2) RoundedCost
FROM Production.ProductCostHistory

The above query returns the standard cost values in the Product Cost History table rounded up to 2 decimal places. The data type of the column Standard Cost is money. For better display and readability, we can use the ROUND function on it, as shown above.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>