Oracle ROUND

The Oracle ROUND() function returns a date rounded to a specific unit.


The following shows the syntax of the Oracle ROUND() function:

ROUND(date, format);
Code language: SQL (Structured Query Language) (sql)


The Oracle ROUND() function takes two arguments:

1) date

The date argument is a DATE value (or an expression which resolves to a DATE value) that should be rounded.

2) format

The format argument is a string format the 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:

CC, SCCCentury, with or without minus sign (BC)
[S]YYYY, [S]YEAR, YYY, YY, YYear (in various appearances)
MONTH, MON, MM, RMMonth (full name, abbreviated name, numeric, Roman numerals)
IW, WW (ISO)week number
WDay of the week
DDD, DD, JDay (of the year/of the month/Julian day)
DAY, DY, DClosest Sunday
HH, HH12, HH24Hours

Return Value

The Oracle ROUND() function always returns a DATE value.


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 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:

Oracle ROUND date function example

In this tutorial, you have learned how to use the Oracle ROUND() function to get a DATE value rounded to a specific unit.

Was this tutorial helpful?