In SQL Server, the T-SQL ROUND() function allows you to round a number to a specified length or precision.

You provide the number as an argument, as well as the length you’d like the number to be rounded to. The function also accepts an optional third argument that allows you to specify whether the number is rounded or truncated.

Syntax

The syntax goes like this:

ROUND ( numeric_expression , length [ ,function ] )  

Where the arguments have the following definitions:

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length

Is the precision to which numeric_expression is to be rounded. This argument must be an expression of type tinyint, smallint, or int. When it’s a positive number, numeric_expression is rounded to the number of decimal positions specified by this argument. When it’s a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by this argument.

function

Is the type of operation to perform. This must be tinyint, smallint, or int. When this argument 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.

Example 1 – Basic Usage

Here’s a basic example to demonstrate how this function works.

SELECT ROUND(1.49, 1) Result;

Result:

+----------+
| Result   |
|----------|
| 1.50     |
+----------+

In this case the number is rounded up.

Example 2 – Zero Decimal Places

If we set the second argument to zero, here’s what happens:

SELECT ROUND(1.49, 0) Result;

Result:

+----------+
| Result   |
|----------|
| 1.00     |
+----------+

This is because we specified zero fractional parts with which to round the number.

And here’s what happens if I change the initial number to 1.50:

SELECT ROUND(1.50, 0) Result;

Result:

+----------+
| Result   |
|----------|
| 2.00     |
+----------+

Example 3 – More Decimal Places

Here’s an example where the number to be rounded contains more decimal places.

SELECT ROUND(1.234, 2) Result;

Result:

+----------+
| Result   |
|----------|
| 1.230    |
+----------+

And here’s what happens when I increase the last digit to 5:

SELECT ROUND(1.235, 2) Result;

Result:

+----------+
| Result   |
|----------|
| 1.240    |
+----------+

Of course, we could use many more decimal places. For example, we could trim a lot of decimal places down.

SELECT ROUND(1.23456789123456789, 8) Result;

Result:

+---------------------+
| Result              |
|---------------------|
| 1.23456789000000000 |
+---------------------+

Example 4 – Truncation (i.e. using a third argument)

As mentioned, we can provide a third argument to specify whether the result is truncated or just rounded. The previous examples are all rounded because we didn’t specify a third argument. When we omit the third argument, a value of 0 is used (which means to round the result). If we provide a value other than 0, the result is truncated.

SELECT 
  ROUND(1.236, 2) 'Rounded (by default)',
  ROUND(1.236, 2, 0) 'Rounded (explicitly)',
  ROUND(1.236, 2, 1) 'Truncated';

Result:

+------------------------+------------------------+-------------+
| Rounded (by default)   | Rounded (explicitly)   | Truncated   |
|------------------------+------------------------+-------------|
| 1.240                  | 1.240                  | 1.230       |
+------------------------+------------------------+-------------+

Example 5 – Using a Negative Value for the Second Argument

Here’s how we can use a negative value on the second argument to cause parts of the non-fractional part to be rounded to zero.

SELECT ROUND(123.456, -1) Result;

Result:

+----------+
| Result   |
|----------|
| 120.000  |
+----------+

And here’s how it gets rounded if we increase the 3 to 5 or more.

SELECT ROUND(125.456, -1) Result;

Result:

+----------+
| Result   |
|----------|
| 130.000  |
+----------+

You can do this even if the number contains no fractional part.

SELECT ROUND(125, -1) Result;

Result:

+----------+
| Result   |
|----------|
| 130      |
+----------+

Example 6 – Rounding Negative Numbers vs Positive Numbers

As seen in the previous examples, when using positive numbers, a value with a fractional part of .5 or greater is rounded up to the next integer.

However, when using negative numbers, such values are rounded down.

SELECT 
  ROUND(1.50, 0) Positive,
  ROUND(-1.50, 0) Negative;

Result:

+------------+------------+
| Positive   | Negative   |
|------------+------------|
| 2.00       | -2.00      |
+------------+------------+