AntDB-Oracle Compatibility Developer’s Manual P3–44
Published: 2023-09-11 05:26:04

##### ROUND

The ROUND function returns a rounded date type value based on the specified template pattern. If no template pattern is specified, then the date is rounded to the nearest day. The following table shows the template patterns used by the ROUND function.

| Mode| Description
|----------|----------
| CC, SCC| If the last 2 digits are less than or equal to 50, return the 1st of January, in cc01 cc is the first 2 digits of the given year. And if the last 2 digits are greater than 50, add 1 to the first 2 digits of the given year; (for AD years)
| SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y| Return to January 1, in yyyy, yyyy is rounded to the year closest to it, forward to June 30, backward to July 1
| IYYY, IYY, IY, I| Rounding the date to the beginning of the ISO year is forward if the month and date are before June 30th, and backward if the month and date are after July 1st.
| Q| Determine the first day of the quarter by rounding forward if the month and day are before the 15th of the second month of each quarter, or rounding backward if the month and day are on the 16th of the second month of each quarter or later.
| MONTH, MON, MM, RM| If the day of the month is on or before the 15th of the month, then the first day of the specified month is returned; if the day of the month is on or after the 16th, then the first day of the next month is returned.
| WW| Round to the nearest date that corresponds to the same day of the week as the first day of the year.
| IW| Round to the nearest date that corresponds to the date of the same week as the first day of the ISO year.
| W| Round to the nearest date that corresponds to the same day of the week as the first day of the month.
| DDD, DD, J| If the date is 11:59:59AM or earlier, the date is rounded to the start of the same day, and if the date is 12:00PM or later, the date is rounded to the start of the next day.
| DAY, DY, D| Round up to the nearest week date to the Sunday date.
| HH, HH12, HH24| Rounding to the nearest hour.
| MI| Take the number of minutes to the nearest whole.

The following is an example of the use of the function ROUND.

The following example rounds the date to the beginning of the nearest century.

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

   Century   
------------------
 01-JAN-2001
(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 example rounds the date to the beginning 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)
```

The following example rounds the date to the nearest ISO year start date. The first example rounds to 2004, which for the 2004 ISO year actually starts on December 29, 2003. The second example rounds the date to 2005. For the ISO year 2005, it actually starts on January 3, 2005. (The ISO year starts on the first Monday in a 7-day span, from Monday to Sunday, and contains at least 4 days in the new year. Thus, 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 example rounds the date to the nearest quarter.

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

       Quarter       
---------------------
 2007-01-01 00:00:00
(1 row)


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

       Quarter       
---------------------
 2007-04-01 00:00:00
(1 row)
```

The following example rounds the date to the nearest month.

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

        Month        
---------------------
 2007-12-01 00:00:00
(1 row)

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

        Month        
---------------------
 2008-01-01 00:00:00
(1 row)
```

The following example rounds the date to the nearest week. So in the first example, the closest Monday to January 18, 2007 is January 15, 2007. In the second example, the closest Monday to January 19, 2007 is January 22, 2007.

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

        Week         
---------------------
 2007-01-15 00:00:00
(1 row)

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

        Week         
---------------------
 2007-01-22 00:00:00
(1 row)
```

The following example rounds the date to the nearest number of weeks in ISO format. The number of weeks in ISO format starts from Monday, and in the first example, the closest Monday to the date January 1, 2004 is December 29, 2003. In the second example, the closest Monday to January 02, 2004 is January 05, 2004.

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

      ISO Week       
---------------------
 2003-12-29 00:00:00
(1 row)

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

      ISO Week       
---------------------
 2004-01-05 00:00:00
(1 row)
```

The following example rounds the date to the nearest week, where the start of the week is considered to be the same as the first day of the month.

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

        Week         
---------------------
 2007-03-08 00:00:00
(1 row)


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

        Week         
---------------------
 2007-03-01 00:00:00
(1 row)
```

The following example rounds the date value to the nearest date.

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

         Day         
---------------------
 2007-08-04 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         
---------------------
 2007-08-05 00:00:00
(1 row)
```

The following example rounds the date value to the nearest day of the week, which is Sunday.

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

     Day of Week     
---------------------
 2007-08-05 00:00:00
(1 row)

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

     Day of Week     
---------------------
 2007-08-12 00:00:00
(1 row)
```

The following example rounds 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 example rounds the date value 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)
```