The Oracle ROUND() function returns a date rounded to a specific unit.
Syntax
The following shows the syntax of the Oracle ROUND() function:
ROUND(date, format);Code language: SQL (Structured Query Language) (sql)Arguments
The Oracle ROUND() function takes two arguments:
1) date
The date argument is a DATE value (or an expression that resolves to a DATE value) that should be rounded.
2) format
The format argument is a string format that specifies which unit the date should be rounded to.
The format argument is optional. If you omit it, the ROUND() function will round the date to the nearest day.
The following table illustrates the permitted format models for the format argument:
| Format | Description |
|---|---|
| CC, SCC | Century, with or without minus sign (BC) |
| [S]YYYY, [S]YEAR, YYY, YY, Y | Year (in various appearances) |
| IYYY, IYY, IY, I | ISO year |
| Q | Quarter |
| MONTH, MON, MM, RM | Month (full name, abbreviated name, numeric, Roman numerals) |
| IW, WW (ISO) | week number |
| W | Day of the week |
| DDD, DD, J | Day (of the year/of the month/Julian day) |
| DAY, DY, D | Closest Sunday |
| HH, HH12, HH24 | Hours |
| MI | Minutes |
Return Value
The Oracle ROUND() function always returns a DATE value.
Examples
Suppose we have a date-time value as follows:
'20-Jul-2017 16:30:15'Code language: SQL (Structured Query Language) (sql)To convert this date-time value in the character string type to a value in the DATE type, you use the TO_DATE() function.
TO_DATE( '20-Jul-2017 16:30:15', 'DD-Mon-YYYY HH24:MI:SS' )
Code language: SQL (Structured Query Language) (sql)All the following examples will use this date value for demonstration.
A) Rounding using the default format
The following example rounds the date ( 20-Jul-2017 16:30:15) to the nearest day:
SELECT
TO_CHAR(
ROUND( TO_DATE( '20-Jul-2017 16:30:15', 'DD-Mon-YYYY HH24:MI:SS' ) ),
'DD-Mon-YYYY HH24:MI:SS' ) rounded_result
FROM
dual;Code language: JavaScript (javascript)Here is the result:
In this tutorial, you have learned how to use the Oracle ROUND() function to get a DATE value rounded to a specific unit.
