Oracle SQL ROUND Function returns a number rounded to specific number of decimal places. Oracle ROUND Function can also be used with dates to return the nearest day, month or year etc.
Oracle SQL ROUND Function Syntax
SELECT ROUND(N,D) FROM table_name;
In the above Oracle ROUND Syntax;
- N is the number to be rounded
- D is the decimal places up to which N is to be rounded.
Oracle SQL ROUND Decimal – Using Positive Number Example
Oracle ROUND Function rounds a number to specific decimals.
For example, the SQL ROUND Decimal query below rounds the number ‘1.23456’to 4 places of decimal.
SELECT ROUND(1.23456,4) "ROUND DECIMAL" FROM DUAL;
Above SQL ROUND Decimal query returns “1.2346”.
Note: We have aliased ROUND(1.23456,4) as “ROUND DECIMAL”.
Oracle SQL ROUND Decimal – Using Negative Number Example
Oracle ROUND, rounds even a negative number to specific decimals.
For example, the SQL ROUND Decimal query below rounds the number ‘-1.23456’to 3 places of decimal
SELECT ROUND(-1.23456,3) "Oracle ROUND DECIMAL" FROM DUAL;
Above SQL ROUND Decimal query returns “-1.235”.
Note: We have aliased ROUND(-1.23456,3) as “Oracle ROUND DECIMAL”.
Oracle SQL ROUND Function – Using Date Example (Rounding to Year)
Oracle ROUND can also be used with dates.
For example, Oracle ROUND Function query below returns ‘1-Jan-2013’ as the nearest rounding to year.
SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR') "Oracle ROUND Function"
FROM DUAL;
More on Using Oracle PL/SQL ROUND Function With Dates.
Oracle SQL ROUND Function – Using Date Example (Rounding to Month)
For example, Oracle ROUND Function query below returns ‘1-September-2012’ as the nearest rounding to month.
SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH') "Oracle ROUND Function"
FROM DUAL;
More on Using Oracle PL/SQL ROUND Function With Dates.