The ROUND function returns rounded date values by using specified template formats.

If the template format is not specified and the parameter type is DATE or TIMESTAMP, the function rounds the date up to the nearest day. If the parameter type is INTERVAL, the function extracts the numbers of months and days from the date value. Then, the function calculates the total number of days based on the assumption that each month has 30 days, and returns the result.

The following table shows the template formats that can be used for the ROUND function.

Table 1. Date templates for the ROUND function
Format Description
CC, SCC Returns January 1, CC01. If the two-digit number after CC is less than or equal to 50, CC is the first two digits of the specified year. If the two-digit number after CC is greater than 50, CC is the first two digits of the specified year plus 1.
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Returns January 1, YYYY. If the date is June 30 or earlier of the specified year, the function returns January 1 of the specified year. If the date is July 1 or later of the specified year, the function returns January 1 of the next year.
IYYY, IYY, IY, I Returns the start date of the ISO year. If the date is June 30 or earlier, the function returns the start date of the current ISO year. If the date is July 1 or later, the function returns the start date of the next ISO year.
Note Each ISO year begins on the first Monday of the year. In an ISO year, Monday is the first day of a week and each week has seven days from Monday to Sunday. The first week contains at least four days of the new year. Therefore, an ISO year can start from December of the previous year.
Q Returns the first day of a quarter. If the date is the 15th day or earlier of the second month of the quarter, the function returns the first day of the current quarter. If the date is the 16th day or later of the second month of the quarter, the function returns the first day of the next quarter.
MONTH, MON, MM, RM Returns the first day of a month. If the date is the 15th day or earlier of a month, the function returns the first day of the current month. If the date is the 16th day or later of a month, the function returns the first day of the next month.
WW Returns the week that is the nearest to the current date.
IW Rounds the date to the nearest ISO week.
W Rounds the date value to the nearest week. The start date of the week is considered as the first day of the month.
DDD, DD, J Rounds the date value to the nearest day.
DAY, DY, D Rounds the date value to the nearest Sunday.
HH, HH12, HH24 Rounds the date value to the nearest hour.
MI Rounds the date value to the nearest minute.

The following examples are used to describe how to use the ROUND function.

In the following examples, each date value is rounded to the start year of the nearest century. 

SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century
-------------
 01-JAN-1901
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century
-------------
 01-JAN-2001
(1 row)

In the following examples, each date value is rounded to the start day of the nearest year. 

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year
-------------
 01-JAN-1999
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year
-------------
 01-JAN-2000
(1 row)
In the following examples, each date value is rounded to the start day of the nearest ISO year. In the first example, the date value is rounded to 2004. The ISO year for 2004 begins on December 29, 2003. In the second example, the date value is rounded to 2005. The ISO year for 2005 begins on January 3 of the same year.
Note Each ISO year begins on the first Monday of the year. In an ISO year, Monday is the first day of a week and each week has seven days from Monday to Sunday. The first week contains at least four days of the new year. Therefore, an ISO year can start from December of the previous year. 
SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year
-------------
 29-DEC-2003
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year
-------------
 03-JAN-2005
(1 row)

In the following examples, each date value is rounded to the nearest quarter.

SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

      Quarter
--------------------
 01-JAN-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

      Quarter
--------------------
 01-APR-07 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest month.

SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

       Month
--------------------
 01-DEC-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

       Month
--------------------
 01-JAN-08 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest week. Therefore, in the first example, the date of Monday that is the closest to January 18 is January 15. In the second example, the date of Monday that is the closest to January 19, 2007 is January 22, 2007. 

SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week
--------------------
 15-JAN-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week
--------------------
 22-JAN-07 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest ISO week. An ISO week starts from Monday. In the first example, the date of Monday that is the closest to January 1, 2004 is December 29, 2003. In the second example, the date of Monday that is the closest to January 2, 2004 is January 5, 2004. 

SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week
--------------------
 29-DEC-03 00:00:00
(1 row)

SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week
--------------------
 05-JAN-04 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest week where the start day of a week is considered as the first day of a month. 

SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week
--------------------
 08-MAR-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week
--------------------
 01-MAR-07 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest day.

SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

        Day
--------------------
 04-AUG-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

        Day
--------------------
 05-AUG-07 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest date of Sunday.

SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

    Day of Week
--------------------
 05-AUG-07 00:00:00
(1 row)

SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

    Day of Week
--------------------
 12-AUG-07 00:00:00
(1 row)

In the following examples, each date value is rounded to the nearest hour.

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

        Hour
--------------------
 09-AUG-07 08:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

        Hour
--------------------
 09-AUG-07 09:00:00
(1 row)

In the following examples, each date value is rounded to the nearest minute.

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

       Minute
--------------------
 09-AUG-07 08:30:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

       Minute
--------------------
 09-AUG-07 08:31:00
(1 row)

In the following examples, each date value of the INTERVAL type is rounded to an integer.

SELECT ROUND(TIMESTAMP '2020-10-10 10:22:22' - TIMESTAMP '2020-10-05 12:22:22');
 round 
-------
     4
(1 row)

SELECT ROUND(INTERVAL '1 year 13 months 3 days');
 round 
-------
   753
(1 row)