SQL Server : Round function

In this article I am going to explain the ROUND() function of SQL Server.

ROUND function returns the rounded value of numeric field to the specified length or precision. ROUND function accepts 3 parameters.

SYNTAX :

ROUND (numeric_expression , length [ ,function ] )  

 

1.      numeric_expression : It is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

2.      Length: It is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

3.      Function: It is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated. This is an optional parameter. 

 Example:

declare @value decimal(10,2)=163.346
SELECT ROUND(@value,0)
--Result : 163.00
SELECT ROUND(@value,1)
--Result : 163.40
SELECT ROUND(@value, 2);
--Result : 163.35

SELECT ROUND(@value, -1);
--Result : 160.00

SELECT ROUND(@value, -2);
--Result : 200.00