The Oracle ROUND function is one of those functions that is used a lot and performs similar in many languages. Let’s take a look at the ROUND function and some examples.

## Purpose of the Oracle ROUND Function

The purpose of the Oracle 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.

## Oracle ROUND Syntax

The syntax for the ROUND function is:

`ROUND( input, roundto)`

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

## Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

## Parameters

The parameters of the Oracle ROUND function are:

**input**(mandatory): This is the value that is rounded. It can be any numeric data type, or any date data type.**roundto**(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.

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 Oracle 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)*roundto*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
*input*values, the value is rounded to the nearest even number.

## ROUND with Dates

If you are using the Oracle 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 Oracle?

The Oracle 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 Oracle, you would use the **CEILING** function. This function rounds a number up to the specified value, so you can use the CEILING function to round all of your values up.

A detailed description and examples of this function is coming soon.

## How Can Oracle 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.

A detailed description and examples of this function is coming soon.

## How Can Oracle 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 FROM dual;`

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, then multiplying causes the result to be rounded to the nearest 5.

## How Can Oracle Round To The Nearest 100?

To round to the nearest 100, you can use a **negative value for the roundto parameter** in the Oracle 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) FROM dual;`

Result: 12300

See the examples below for more information.

**Examples of the Oracle ROUND Function**

Here are some examples of the Oracle ROUND function. I find that examples are the best way for me to learn about code, even with the explanation above.

### Example 1

This example rounds a decimal number to 1 place.

```
SELECT ROUND(3162.845, 1) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

3162.8 |

### Example 2

This example rounds a decimal to 5 places.

```
SELECT ROUND(3162.8451297, 5) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

3162.84513 |

### Example 3

This example rounds a decimal to -2 places.

```
SELECT ROUND(3162.845, -2) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

3200 |

### Example 4

This example rounds a whole number to -3 places.

```
SELECT ROUND(142786, -3) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

143000 |

### Example 5

This example rounds a decimal to 0 places.

SELECT ROUND(3162.845, 0) AS ROUNDED

FROM dual;

Result:

ROUNDED |

3163 |

### Example 6

This example rounds a decimal with no roundto parameter specified.

```
SELECT ROUND(3162.845) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

3163 |

### Example 7

This example rounds a number to the nearest 5.

```
SELECT ROUND(3162.845/5)*5 as ROUNDED
FROM dual;
```

Result:

ROUNDED |

3165 |

### Example 8

This example rounds a number to the nearest hundred.

```
SELECT ROUND(142786, -2) AS ROUNDED
FROM dual;
```

Result:

ROUNDED |

142800 |

### Example 9

This example rounds a date to the nearest year.

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

Result:

SYSDATE |
ROUNDED |

02/APR/15 | 01/JAN/15 |

### Example 10

This example rounds a date to the nearest month.

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

Result:

SYSDATE |
ROUNDED |

02/APR/15 | 01/APR/15 |

## 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.

## Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

*Image courtesy of digitalart / FreeDigitalPhotos.net*

### Oracle SQL Function Cheat Sheet

Subscribe and get **a PDF cheat sheet** of all of the **Oracle SQL functions** and parameters for easy reference!