SQL Server Round Function

Numbers are everywhere, and as developers and database administrators, we cannot escape the use of numbers. We constantly need to modify and manipulate numerical values to fit specific requirements.

For that case, SQL Server provides a set of features and functions for performing arithmetic and numerical operations. In this guide, we will look at one useful function that allows us to format a decimal number to a specific precision.

Basic Usage

The SQL Server round() function allows you to provide a decimal value and a precision point. The function will return the number rounded to the defined precision point.

The function syntax is as:

round(numeric_expression, precision_point, [operation]);

Function Arguments and Return Value

The round function accepts three arguments as:

1. numeric_expression – this defines the number to be rounded off by the function.
2. precision_point – the number of decimal places to round off the numeric_expression.
3. operation – The operation parameter is optional and is used to truncate a value to the specified number.

The function will return the number rounded to the specified precision value.

SQL Server Round() Function Examples

The following examples show how to use the round function in SQL Server.

Example 1:

The example below shows how to use the round function to one decimal value.

SELECT round(456.789, 1);

The output is as:

---------------------------------------
456.800

Example 2:

By default, the operator parameter of the round function is set to 0. If the value is 0, the function performs a round-off, while if the value is above 0, the function performs a truncation to the specified point.

For example, the following shows when the value is set to 0.

SELECT round(456.789, 2, 0);

The output is as:

----------------
456.790

However, if we set the third parameter to 3, we get the value:

SELECT round(456.789, 2, 3);
------------
456.780

Here, the function does not round off the value. Instead, it performs a truncation to 3 precision points.

Example 3:

What happens when you provide a negative value as the precision point? Consider the example shown below:

SELECT round(456.789, -2);

In this example, the function rounds off the value to the nearest hundred as:

-----------
500.000

Example 4:

The same case applies when performing the above operation on a negative integer.

Consider the example below:

SELECT round(-456.789, -1);

The function rounds off the number to the nearest tenth in such a case. An example output is as:

------------
-460.000

Example 5:

The round function accepts whole numbers. For example, the following query truncates the value to 3 precisions.

SELECT round(4560, -3, 3);

The output is as:

------
4000

Closing

In this guide, we discovered how to perform rounding and truncation operations on numerical values in SQL Server. The round() function is convenient when setting a precision point for various values in your database.

We hope you enjoyed the article, thanks.

from https://ift.tt/3KncahG