How to use Oracle ROUND Function | Round(Number/Date)

In Oracle, the Oracle Round function is used to round a numeric value to a specific number of the nearest integer or the nearest decimal value. I have listed 10 different examples of different usage of the Oracle round function. This is using all the Oracle programmers’ programming work there for it is a hugely used function in Oracle.

-- Syntax
ROUND(numeric_expression [, precision]) 
•	numeric_expression: Value use for rounding.
•	precision: (Optional) Define the number of precisions to rounding. If there are nothing, then rounding into integer otherwise its rounding according to pressions.
--

Decimal numbers maintain up to 14 digits of precision

1: Basic Rounding to Nearest Integer

Basic usage of the ROUND function. Rounding into the nearest integer if you don’t have any precision defined as a parameter.

--
SELECT ROUND(15.68) AS rounded_value FROM dual; 
-- Output: 
rounded_value 
16 
--

2: Rounding to Specified Decimal Places

Value rounding into the nearest decimal number according to precision.

--
SELECT ROUND(15.715, 2) AS rounded_value FROM dual;
-- Output: 
rounded_value 
15.72 
--

3: Rounding Negative Values

Negative decimal values also round into the nearest decimal point as defined.

--
SELECT ROUND(-15.785, 1) AS rounded_value FROM dual; 
-- Output: 
rounded_value 
15.8 
--

4: Rounding Date Values

Are you using ROUND for dates if not it is possible. Round returns the nearest date according to the specified format. Every time return date value. If you do not specify the format then default returns the nearest day as the value.

--
SELECT ROUND (TO_DATE ('27-OCT-24'),'YEAR')   "New Year" FROM DUAL;
 
New Year
---------
1/1/2025

SELECT sysdate+10, ROUND (sysdate+10)   "Nearest" FROM DUAL;

sysdate+10,             Nearest
----------------------------------
3/9/2024 12:48:54 AM,  3/9/2024

5: Rounding Large Numbers

Round large decimals up to 14 digits are also similar to the basic rounding mechanism.

--
SELECT ROUND(123456789.123456789, 4) AS rounded_value FROM dual; 
-- Output: 
rounded_value 
123456789.1235
--

6: Rounding Up and Down

As you know rounding value between 0-4 then rounding to down else 5-9 rounding into up side.

SELECT ROUND(15.499) AS rounded_value_1, ROUND(15.501) AS rounded_value_2 FROM dual; 
-- Output: 
rounded_value_1 rounded_value_2 
 15               16

7: Rounding with Negative Decimal Places

If you added positive decimal places rounding value is to the right side  also if you have negative decimal places values are round to the left side from the decimal point

--
SELECT ROUND(18.193,1) "Round" FROM DUAL;

     Round
----------
      18.2

below example rounds a number one digit to the left of the decimal point:

SELECT ROUND(18.193,-1) "Round" FROM DUAL;

     Round
----------
        20 

8: Rounding Dates with Timestamps

--
SELECT SYSTIMESTAMP ,ROUND(SYSTIMESTAMP) AS rounded_timestamp FROM dual;
-- Output:
SYSTIMESTAMP,                           rounded_timestamp
28-FEB-24 01.09.51.175000 AM +01:00     2/28/2024
--

9: Rounding NULL Values

Null values are also accepted by the ROUND function

--
SELECT ROUND(NULL) AS rounded_value FROM dual; 
-- Output: 
rounded_value 
 NULL
--

Oracle Round function is a heavily used Oracle-built function and you can use both integer and date-type values for rounding.

Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROUND-number.html#GUID-849F6C45-0D72-4464-9C0F-8B6822BA85E1