Oracle ROUND(date) Function

Oracle ROUND(date) is a built-in function that rounds the given date to the specified unit.

Oracle ROUND(date) Syntax

Here is the syntax for the Oracle ROUND(date) function:

ROUND(date)
ROUND(date, fmt)

Parameters

date

Required. The date/datetime expression to be rounded. It must be a value of type DATE.

fmt

Optional. It specifies the unit to which the rounding is to be done. If this parameter is not specified, date is rounded to the nearest day.

The following table summarizes the values that fmt can take:

Format Model Unit
CC, SCC Century (rounded up to the nearest century with a higher two-digit year)
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Year (rounded up to July 1)
IYYY, IYY, IY, I ISO 8601 year with week of year
Q Quarter (rounded up to the 16th day of the second month of the quarter)
MONTH, MON, MM, RM Month (rounded up to the 16th day)
WW Week of year that is the same as the first day of the year
IW Week of year that is the same as the first day of the ISO 8601 calendar week
W Week of month that is the same as the first day of the month
DDD, DD, J Day
DAY, DY, D Start of the week
HH, HH12, HH24 Hour
MI Minute

Return Value

The Oracle ROUND(date) function returns a rounded value. The return value is always of the DATE data type.

If either parameter is NULL, ROUND(date) returns NULL.

Oracle ROUND(date) Examples

Here are some examples that demonstrate the usage of the Oracle ROUND(date) function.

Basic Usage

The following statement rounds 2023-02-12 11:59:59 to the nearest day, using the default unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  ROUND(TO_DATE('2023-02-12 11:59:59', 'YYYY-MM-DD HH24:MI:SS'))
FROM dual;

Output:

ROUND(TO_DATE('2023-02-1211:59:59','YYYY-MM-DDHH24:MI:SS'))
______________________________________________________________
2023-02-12

Here, since we did not specify the fmt parameter, 2023-02-12 11:59:59 is rounded to the nearest day. And since it is earlier than 12 pm, the time portion is truncated and today is returned.

Let’s take a value that is later than 12 pm:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  ROUND(TO_DATE('2023-02-12 12:01:01', 'YYYY-MM-DD HH24:MI:SS'))
FROM dual;

Output:

ROUND(TO_DATE('2023-02-1212:01:01','YYYY-MM-DDHH24:MI:SS'))
______________________________________________________________
2023-02-13

Here, since the time is past 12 o’clock, it has returned to the next day.

The Oracle ROUND(date) function allows you to round date-time to the specified unit. Please see the example below.

Rounding to Year

The Oracle ROUND(date) function allows you to round date-time to the year, using the YEAR unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  ROUND(DATE '2023-06-30', 'YEAR') "2023-06-30",
  ROUND(DATE '2023-07-01', 'YEAR') "2023-07-01"
FROM dual;

Output:

2023-06-30    2023-07-01
_____________ _____________
2023-01-01    2024-01-01

In this example, 2023-06-30 is rounded down to 2023-01-01, and 2023-07-01 is rounded up to 2024-01-01.

Month

The Oracle ROUND(date) function allows you to round date-time to the month, using the MONTH unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  ROUND(DATE '2023-02-15', 'MONTH') "2023-02-15",
  ROUND(DATE '2023-02-16', 'MONTH') "2023-02-16"
FROM dual;

Output:

2023-02-15    2023-02-16
_____________ _____________
2023-02-01    2023-03-01

In this example, 2023-02-15 is rounded down to 2023-02-01, and 2023-02-16 is rounded up to 2023-03-01.

NULL Parameter

If either parameter is NULL, ROUND(date) will return NULL.

SET NULL 'NULL';
SELECT
    ROUND(NULL, 'YEAR') NULL_1,
    ROUND(DATE '2023-02-15', NULL) NULL_2,
    ROUND(NULL, NULL) NULL_3
FROM dual;

Output:

   NULL_1 NULL_2       NULL_3
_________ _________ _________
     NULL NULL           NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle ROUND(date) is a built-in function that rounds the given date to the specified format.