SQL Server ROUND() Function

In SQL Server, the ROUND() function returns a numeric value, rounded to the specified length.

ROUND(number, length [, function])

Parameters

number: The input number to be rounded.

length: Is the number of decimal places the number is to be rounded. Length can be of type tinyint, smallint or int.

If length is a positive integer, then it specifies the number of decimals the number needs to be rounded.

If length is negative, it specifies the number of digits on the left side of the decimal point of number to be rounded.

function: is an optional parameter. It specifies the type of operation to perform. The default value is 0. It can be a tinyint, smallint or int. If the function parameter is zero or omitted, number is rounded. If the function value is other than zero, number is truncated.

Return Value

Return value can be an int, bigint, decimal, money, or float.

Example 1:

The following example demonstrates the ROUND() function with different length parameters.

Example: ROUND()

SELECT ROUND (123.456, 0) AS Round0, 
       ROUND (123.456, 1) AS Round1, 
       ROUND (123.456, 2) AS Round2

In the above example, it rounds the fraction point of a number by the specified length. If length is 1 then rounded to the first fraction value, if length is 2 then rounds to 2 fraction point, and so on.

Example 2:

In this example, the length parameter is negative.

Example: ROUND()

SELECT ROUND (123.46, 0) AS RoundZero, ROUND (123.46, -1) AS RoundNegative1, ROUND (123.46, -2) AS RoundNegative2

Example 3:

In this example, the function parameter value is set to 1. Here the ROUND() function truncates the input value as shown in the result.

Example: ROUND()

SELECT ROUND (123.56, 0) AS Rounded, ROUND (123.56, 0, 1) AS RoundTruncate

Example 4:

Here, the length parameter is negative and larger than the number of digits before the decimal point. The return value is zero.

Example: ROUND()

SELECT ROUND (123.46, -4) AS Rndlarge

Example 5:

The ROUND() function is used with the Salary column of the Employee table to calculate the bonus.

Example: ROUND()

SELECT EmployeeID, Salary, ROUND ((Salary * 0.75), 2) AS EmpBonus FROM Employee