ROUND () is one of the commonly used MATH functions available in SQL Server. It is used to find the rounded value of the given expression to the specified precision. Any numeric data type can be used as an expression except bit data type.
It takes three parameters; first parameter is the expression of which the rounded value is required and second is the length to which the expression has to be rounded off and the third parameter is the optional one and is the operation to perform on the expression. This function must return a value.
Third parameter must be of int, smallint or tinyint data type. When its value is 0 or it is not defined (default value is taken i.e., 0) it just round off the specified expression. If any value other than 0 (default value) is specified it truncates the value to the specified length.
Syntax:
ROUND (numerical_expression, length, function)
Example:
SELECT ROUND(375.244, 2), ROUND(375.244, -2)
Output:
375.240 400.000
This example shows if the specified length is negative, then the value is rounded off till the specified digits at right hand side. Also, the length in the negative is greater than or equal to the number of digits at right hand side, it gives a result: 0.00 (zero).
Another example to show if “function” parameter is specified in the function:
SELECT ROUND(375.244, 2, 1), ROUND(375.244, -2, 0),
ROUND(375.244, -2, 1)
Output:
375.240 400.000 300.000
If the value of the third parameter is defined ‘1’, the value will be truncated to the specified length, which is -2 here.
Below is the table defined for the returned data type for any specific numeric data type:
Expression result
tinyint
smallint
int
bigint
decimal and numeric category (p, s)
money and smallmoney category
float and real category
Return type
int
int
int
bigint
decimal(38, s)
money
float
