FB

Oracle ROUND FunctionThe 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 Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

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

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

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 Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

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

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

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!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your FREE PDF: 9 Ways to Improve your Database Skills