The SQL ROUND function rounds the specified numeric expression or an individual number to user-specified length or precision. The syntax of the SQL Server ROUND Function is
SELECT ROUND (Numeric_Expression, length, function) FROM [Source]
- Numeric_Expression: A valid numerical expression, or approximate numeric data type expect 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, Round function will round the Numeric_Expression to the number of the decimal point specified as length.
- If the length argument is a negative number, Numeric_Expression rounded 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 if we assign the default 0, then Numeric_Expression will be rounded. If we specify the function value other than 0, then the Numeric_Expression will be truncated.
SQL ROUND Function Example 1
The Mathematical ROUND Function returns the rounded numeric value. Few ROUND function examples are
DECLARE @i float SET @i = 248.6759 SELECT ROUND(@i, 0)AS [Round_Result 1] SELECT ROUND(@i, 1)AS [Round_Result 2] SELECT ROUND(@i, 2)AS [Round_Result 3] SELECT ROUND(@i, 3)AS [Round_Result 4] -- Calculating Round on Negative Values SELECT ROUND(@i, -1)AS [Round_Result 5] SELECT ROUND(@i, -2)AS [Round_Result 6] SELECT ROUND(@i, -3)AS [Round_Result 7] --Truncated Round Values SELECT ROUND(@i, 0, 1)AS [Round_Result 8] SELECT ROUND(@i, 0, 0)AS [Round_Result 9]
Used the SQL Server ROUND function to find the rounded numeric value of the variable @i. We also assigned a new name to that result as ‘Round_Result 1’ using the ALIAS Column in SQL Server.
SELECT ROUND(@i, 0)AS [Round_Result 1]
Next, We used the ROUND Mathematical function with a negative length value.
SELECT ROUND(@i, -1)AS [Round_Result 5]
We used the SQL Round Function to showcase the truncation.
SELECT ROUND(@i, 0, 1)AS [Round_Result 8] SELECT ROUND(@i, 0, 0)AS [Round_Result 9]
ROUND Function Example 2
In this example, We find the rounded numeric values for all the records present in [List Price] using SQL Server ROUND Function.
SELECT [EnglishProductName] ,[Color] ,[StandardCost] ,[ListPrice] ,ROUND([ListPrice], 0) AS [List_Round0] ,ROUND([ListPrice], 2) AS [List_Round2] ,ROUND([ListPrice], -1) AS [List_Round-1] ,ROUND([ListPrice], -2) AS [List_Round-2] ,ROUND([ListPrice], 0, 1) AS [List_Round01] ,[SalesAmount] FROM [SQL Tutorial].[dbo].[Product Sales]