Oracle ROUND

ROUND is a special function used in Oracle to round off the numeric digits after decimal up to n decimal places. If we specify n as 0, then the ROUND function rounds off the value before the decimal point.

The ROUND functions works the same way as rounding off works in mathematics.

Syntax

The syntax of Oracle ROUND function is as follows:

ROUND(expression);

Here,

Expression could be a column name, a decimal or even a date. Lets have a look at the following table to understand how rounding off works

Examples

1. ROUND function with Decimals

We can use ROUND function to round off decimals. Lets have a look at some queries using DUAL table:

select ROUND(84.282,2) FROM dual;
select ROUND(84.288,2) FROM dual;
select ROUND(84.292,0) FROM dual;

Result:

2. Using ROUND function with Columns in a Table:

For this example, we will be using Employee table from HR Schema,

We will the following query, to round off the salary of employees in thousands to two digits.

SELECT 
    first_name, last_name , 
    ROUND(salary/1000, 2) "SALARY (IN THOUSANDS)"
FROM employees ;

Result:

Using this query we were able to first convert the salary to decimals and then round it off to two digits.

3. ROUND function with Dates

We can also use ROUND function with dates, but we first need to know the formats in which dates can be converted.

Have a look at this table to understand the date formats:

Suppose, we have a date time value as below:

’14-Feb-2011 14:30:16′

First, we need to convert this value to DATE data type as this value is considered a string of characters. For this we need to use, TO DATE function.

TO_DATE( '14-Feb-2011 14:30:16', 'DD-Mon-YYYY HH24:MI:SS' )

This function converts the character string to DATE data type in the format we want.

Using ROUND Function with date

Lets round off our date value to nearest date:

SELECT
TO_CHAR(
ROUND( TO_DATE( '14-Feb-2011 14:30:16', 'DD-Mon-YYYY HH24:MI:SS' ),
'DD-Mon-YYYY HH24:MI:SS' ) rounded_result
FROM
dual;

Result:

As we can see from the result, the results are rounded off to the nearest date which is 15 Feb 2011.

In this tutorial, we learned how to use the Oracle ROUND function to round off decimals. values in columns as well as dates.