SQL ROUND Function

This SQL Server function rounds the specified numeric expression or an individual number to user-specified length or precision. The syntax of the SQL ROUND Function is shown below.

SELECT ROUND (Numeric_Expression, length, function)
FROM [Source]

Numeric_Expression: A valid numerical expression or approximate numeric data type except for bit type.
Length: It is the precision to which the Numeric_Expression rounded. Length can be a number or a valid numerical expression of type tinyint, smallint, or int.
- If the length argument is a positive number, this SQL Server Round function will circle the Numeric_Expression to the number of the decimal point specified as length.
- If the length argument is a negative number, the Numeric_Expression value goes to the left side of the decimal point.
Function: This is an optional parameter, and it must be type int, tinyint, or smallint. If we omit this parameter or assign the default 0, then Numeric_Expression will remove all decimals. If we specify a function value other than 0, the Numeric_Expression truncates.

SQL Server ROUND Function Example

This Mathematical Function returns the rounded numeric value. In the following example, we used the SQL ROUND function to find the numeric value of the variable @i.

We also assigned a new name to that result as ‘Result 2’ using the ALIAS Column in SQL Server.

For the Negative One, We used this Mathematical method with a negative length value.

Within Trunc 1 and 2, we used this math method to showcase the truncation.

DECLARE @i float
SET @i = 248.6759

SELECT ROUND(@i, 1)AS [Result 2]
SELECT ROUND(@i, 2)AS [Result 3]
SELECT ROUND(@i, 3)AS [Result 4]

-- Calculating for Negative Values
SELECT ROUND(@i, -1)AS [Negative One]
SELECT ROUND(@i, -2)AS [Negative Two]
SELECT ROUND(@i, -3)AS [Negative Three]

--Truncated Values
SELECT ROUND(@i, 0, 1)AS [Trunc 1]
SELECT ROUND(@i, 0, 0)AS [Trunc 2]

Round Example 2

In this example, we find the polished numeric values for all the records present in [List Price] using SQL ROUND Function.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,ROUND([ListPrice], 2) AS [TWO] 
      ,ROUND([ListPrice], -1) AS [-ONE] 
      ,ROUND([ListPrice], -2) AS [-TW0] 
      ,ROUND([ListPrice], 0, 1) AS [3 ARGS] 
      ,[SalesAmount]
FROM [Product Sales]