The SQL ROUND function allows you to round a number to a specific number of decimal places. Let’s see how it works and see some examples.

This article applies to Oracle, SQL Server, MySQL, and Postgres.

## Purpose of the SQL ROUND Function

The purpose of the SQL ROUND function is to **take a number and round it to the specified number of decimal places**. There are a lot of similar functions, but the ROUND function is probably the most common and it’s pretty easy to use.

ROUND can also be used with date input values. You can supply a date value to it, as well as the format, and it will round the date to that format.

## SQL ROUND Syntax

The syntax for the ROUND function in Oracle, MySQL and Postgres is::

`ROUND (input, decimals)`

In SQL Server, there is a third parameter:

`ROUND (input, decimals [, operation])`

Let’s take a look at what these values are used for.

## Parameters

The parameters of the SQL ROUND function are:

**input**(mandatory): This is the value that is rounded. It can be any numeric data type or any date data type.**decimals**(optional): This determines what the*input*value is rounded to. If it is a number, then you specify the decimal places here. If it is a date, you specify the part of the date.**operation**(optional): This parameter in SQL Server allows you to specify either 0 or omit the value, the input is rounded. If you specify 1, the value is truncated.

This function can be used with both dates and numbers. What kind of input does it need?

## ROUND with Numbers

If you are using the SQL ROUND function with numbers, then:

**input**needs to be a numeric data type or a value that can be converted to a numeric data type (such as a number inside a VARCHAR value)**decimals**needs to be an integer.- If
**roundto**is positive, the result is rounded to the right of the decimal point - If
**roundto**is negative, the result is rounded to the left of the decimal point - If
**roundto**is 0 or omitted, the result is rounded to 0 decimal places - If
**roundto**is omitted, the result is in the same data type as the*input*value - If
**roundto**is specified, the result is in a NUMBER data type - For NUMBER
**input**values, the value is rounded away from 0 (e.g. 2.5 rounds to 3, -1.5 rounds to 2). - For BINARY_FLOAT and BINARY_DOUBLE
*∫ˆ*values, the value is rounded to the nearest even number.

## ROUND with Dates

If you are using the ROUND function with dates, then:

**input**needs to be a date or datetime with timestamp**roundto**can be any valid format mask (e.g. year, month, day, week, minute)- If
**roundto**is omitted, the**input**is rounded to the nearest day - The value returned is always of data type DATE, even if you specify a different datetime data type.

## How Do You Always Round Up In SQL?

The ROUND function will round the number up or down, depending on the decimal places specified. It **doesn’t always round up**, as you can see in the examples below.

If you want to always round up in SQL, you would use the **CEIL** function. This function rounds a number up to the specified value, so you can use the CEIL function to round all of your values up.

You can read my guide on the CEIL function here.

## How Can SQL Round Down 2 Decimal Places?

You can use the ROUND function to round to 2 decimal places, by specifying the number 2 as the second parameter. However, this will **not always round down**. It could round up, depending on your input value.

To always round down, you would use the **FLOOR** function, which is the opposite of the CEILING function mentioned above.

You can read my guide on the FLOOR function here.

## How Can SQL Round To The Nearest 5?

Yes, you can do this with ROUND.

To round to the nearest 5, you use the ROUND function and divide your number by 5, then multiply it by 5.

For example:

`SELECT ROUND(3162.845 / 5) * 5;`

Result:

3165

This function first doubles the number you wish to round, and then rounds it. Then, it halves the result. So, this example would perform these steps:

- 3162.845 / 5 =632.569
- ROUND(632.569) = 633
- 633 * 5 = 3165

The action of dividing, rounding, and then multiplying causes the result to be rounded to the nearest 5.

## How Can You Round To The Nearest 100?

To round to the nearest 100, you can use a **negative value for the roundto parameter** in the ROUND function. The negative value will round to the left of the decimal place, so you can get a value that is rounded to the nearest 100.

`SELECT ROUND(12345.67, -2);`

Result:

12300

See the examples below for more information.

**Examples of the SQL ROUND Function**

Here are some examples of the SQL ROUND function.

### Example 1

This example rounds a decimal number to 1 place.

`SELECT ROUND(3162.845, 1);`

Result:

3162.8

### Example 2

This example rounds a decimal to 5 places.

`SELECT ROUND(3162.8451297, 5);`

Result:

3162.84513

### Example 3

This example rounds a decimal to -2 places.

`SELECT ROUND(3162.845, -2);`

Result:

3200

### Example 4

This example rounds a whole number to -3 places.

`SELECT ROUND(142786, -3);`

Result:

143000

### Example 5

This example rounds a decimal to 0 places.

`SELECT ROUND(3162.845, 0);`

Result:

3163

### Example 6

This example rounds a decimal with no *roundto* parameter specified.

`SELECT ROUND(3162.845);`

Result:

3163

### Example 7

This example rounds a number to the nearest 5.

`SELECT ROUND(3162.845/5)*5;`

Result:

3165

### Example 8

This example rounds a number to the nearest hundred.

`SELECT ROUND(142786, -2);`

Result:

142800

### Example 9

This example rounds a date to the nearest year, using Oracle’s SYSDATE function.

```
SELECT
SYSDATE,
ROUND(SYSDATE, 'YEAR') AS ROUNDED;
```

Result:

SYSDATE |
ROUNDED |

02/APR/22 | 01/JAN/22 |

### Example 10

This example rounds a date to the nearest month, using Oracle’s SYSDATE function.

```
SELECT
SYSDATE,
ROUND(SYSDATE, 'MONTH') AS ROUNDED;
```

Result:

SYSDATE |
ROUNDED |

02/APR/22 | 01/APR/22 |

## Similar Functions

Some functions which are similar to the ROUND function are:

- CEILING – This function rounds a number up to the specified precision
- FLOOR – This function rounds a number down to the specified precision
- TRUNC – This function truncates either a number or a date value to the specified precision

You can find a full list of Oracle functions here.