The ROUND function returns a date rounded according to a specified template pattern. If the template pattern is omitted, the date is rounded to the nearest day. The following table shows the template patterns that can be used for the ROUND function.

Table 1. Template date patterns for the ROUND function
Pattern Description
CC, SCC Returns January 1, cc01 where cc is the first 2 digits of the given year if the last 2 digits are at most 50, or 1 greater than the first 2 digits of the given year if the last 2 digits are greater than 50.
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Returns January 1, yyyy where yyyy is rounded to the nearest year. The date rounds down on June 30, and rounds up on July 1.
IYYY, IYY, IY, I Rounds the date to the beginning of the ISO year, which is determined by rounding down if the month and day is on or before June 30. The date rounds up if the month and day is July 1 or later.
Q Returns the first day of the quarter, which is determined by rounding down if the month and day is on or before the 15th day of the second month of the quarter. Otherwise, the date is rounded up if the month and day is the 16th day of the second month or later of the quarter.
MONTH, MON, MM, RM Returns the first day of the specified month if the day of the month is on or before the 15th day. Returns the first day of the following month if the day of the month is the 16th day or later.
WW Rounds the date to the nearest date that corresponds to the same day of the week as the first day of the year.
IW Rounds the date to the nearest date that corresponds to the same day of the week as the first day of the ISO year.
W Rounds the date to the nearest date that corresponds to the same day of the week as the first day of the month.
DDD, DD, J Rounds the date to the start of the nearest day. Rounds to the start of the same day if the specified time is 11:59:59 AM or earlier. Rounds to the start of the next day if the specified time is 12:00:00 PM or later.
DAY, DY, D Rounds the date to the nearest Sunday.
HH, HH12, HH24 Rounds the date to the nearest hour.
MI Rounds the date to the nearest minute.

The following section provides ROUND function examples.

The following examples round the date to 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)	

The following examples round the date to 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)	
The following examples round the date to the nearest ISO year. The first example rounds to 2004. The ISO year for 2004 begins on December 29, 2003. The second example rounds the date to 2005. The ISO year for 2005 begins on January 3 of that same year.
Note An ISO year begins on the first Monday from which a seven day span (Monday to Sunday) contains at least 4 days of the new year. Therefore, the beginning of an ISO year can start in 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)	

The following examples round the date 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)	

The following examples round the date 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)

The following examples round the date to the nearest week. The first day of 2007 is a Monday. Therefore, in the first example, the Monday that is closest to January 18 is January 15. In the second example, the Monday that is closest to January 19 is January 22.

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)	

The following examples round the date to the nearest ISO week. An ISO week starts on a Monday. In the first example, the Monday that is closest to January 1, 2004 is December 29, 2003. In the second example, the Monday that is 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 TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

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

The following examples round the date to the nearest week where a week is considered to start on the same day as the first day of the 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)	

The following examples round the date 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)

The following examples round the date to the nearest 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)

The following examples round the date 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)

The following examples round the date 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)