SQL Server ROUND() Function

The SQL Server ROUND() function rounds off a specified number to a decimal place. If the length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

ROUND in SQL Server

The ROUND() function in SQL Server rounds off a number to a specified decimal place. It accepts positive, negative, and zero values.

This function always returns the number after rounding to the specified decimal places.

Syntax

The ROUND function syntax is:

ROUND(number, decimals, operation)

Parameters

This method accepts three parameters, as given below :

• number: The specified number to be rounded off.
• decimals: A specified number of decimal places up to which the given number is to be rounded.
• operation: This is an optional parameter. If its value is 0, it rounds the result to the number of decimals. If another value is greater than 0, it truncates the result to the number of decimals. The default value is 0

SQL Server ROUND Function Examples

Let's look at some example of the ROUND function in SQL Server.

Example 1:

In this example, we are rounding off a number up to next two decimal places.

Query

SELECT ROUND(12.3456, 2);

Output :

12.3500

Example 2:

In this example, we are rounding off the number to the next two decimal places with the operational parameter 1 which says only to truncate the specified number (-23.456) to the given decimal places i.e., 2.

Query:

SELECT ROUND(12.3456, 2, 1);

Output :

12.3400

Example 3:

In this example, we are using ROUND() function with negative paramter.

Query:

DECLARE @Parameter_Value FLOAT;
SET @Parameter_Value = -2;
SELECT ROUND(123.4567, @Parameter_Value);

Output :

100.0000

Example 4:

In this example, we are rounding number to the zero number of decimal places.

Query:

SELECT ROUND(123.467, 0);

Output :

123.000

Important Points About SQL Server ROUND Function

• The ROUND() function in SQL Server is used to round off a specified number to a specified number of decimal places.
• It accepts various types of numbers, including positive, negative, and zero.
• The return type of the ROUND() function depends on the input number type.
• The ROUND() function uses the rounding-to-the-nearest-digit algorithm.