Oracle 23c ROUND with DATE data type

Question

I have encountered an odd behaviour of Oracle 23c. As far as I understand lack of the second parameter in the ROUND function should work exactly the same as putting there explicitly 'DAY' parameter.

  SELECT TO_DATE('2024-06-26 15:02:18','YYYY-MM-DD HH24:MI:SS')               AS result                     
     , ROUND(TO_DATE('2024-06-26 15:02:18','YYYY-MM-DD HH24:MI:SS')     )         AS default_result
     , ROUND(TO_DATE('2024-06-26 15:02:18','YYYY-MM-DD HH24:MI:SS')   , 'DAY')    AS day_explicit
  ;

According to the Oracle documentation "If you omit fmt, then date is rounded to the nearest day" and 'DAY' parameter means simply DAY.

The result looks like this -> Shouldn't day_explicit column be like 2024-06-27?

Answer 1

ROUND(date_value, 'DAY') is rounding to the nearest start of the week (as defined by the NLS_TERRITORY session/database parameter).

ROUND(date_value, 'DD') is rounding to the nearest day.

This is documented in the ROUND and TRUNC Date Functions documentation:

Format Model	Rounding or Truncating Unit
DDD DD J	Day
DAY DY D	Starting day of the week

---

For example:

If you have the sample data:

CREATE TABLE table_name (dt) AS
SELECT TRUNC(DATE '2024-06-26', 'IW') + LEVEL - 1 + INTERVAL '15:02:18' HOUR TO SECOND
FROM   DUAL
CONNECT BY LEVEL <= 7;

And you use:

ALTER SESSION SET NLS_TERRITORY = 'Germany'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

Then the output is:

DT	DEFAULT_RESULT	DAY_EXPLICIT	DD_EXPLICIT
2024-06-24 15:02:18 (MON)	2024-06-25 00:00:00 (TUE)	2024-06-24 00:00:00 (MON)	2024-06-25 00:00:00 (TUE)
2024-06-25 15:02:18 (TUE)	2024-06-26 00:00:00 (WED)	2024-06-24 00:00:00 (MON)	2024-06-26 00:00:00 (WED)
2024-06-26 15:02:18 (WED)	2024-06-27 00:00:00 (THU)	2024-06-24 00:00:00 (MON)	2024-06-27 00:00:00 (THU)
2024-06-27 15:02:18 (THU)	2024-06-28 00:00:00 (FRI)	2024-07-01 00:00:00 (MON)	2024-06-28 00:00:00 (FRI)
2024-06-28 15:02:18 (FRI)	2024-06-29 00:00:00 (SAT)	2024-07-01 00:00:00 (MON)	2024-06-29 00:00:00 (SAT)
2024-06-29 15:02:18 (SAT)	2024-06-30 00:00:00 (SUN)	2024-07-01 00:00:00 (MON)	2024-06-30 00:00:00 (SUN)
2024-06-30 15:02:18 (SUN)	2024-07-01 00:00:00 (MON)	2024-07-01 00:00:00 (MON)	2024-07-01 00:00:00 (MON)

Since in most of Europe, the start of the week is Monday.

If you use the same query in a different territory:

ALTER SESSION SET NLS_TERRITORY = 'America'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

Then the output is:

DT	DEFAULT_RESULT	DAY_EXPLICIT	DD_EXPLICIT
2024-06-24 15:02:18 (MON)	2024-06-25 00:00:00 (TUE)	2024-06-23 00:00:00 (SUN)	2024-06-25 00:00:00 (TUE)
2024-06-25 15:02:18 (TUE)	2024-06-26 00:00:00 (WED)	2024-06-23 00:00:00 (SUN)	2024-06-26 00:00:00 (WED)
2024-06-26 15:02:18 (WED)	2024-06-27 00:00:00 (THU)	2024-06-30 00:00:00 (SUN)	2024-06-27 00:00:00 (THU)
2024-06-27 15:02:18 (THU)	2024-06-28 00:00:00 (FRI)	2024-06-30 00:00:00 (SUN)	2024-06-28 00:00:00 (FRI)
2024-06-28 15:02:18 (FRI)	2024-06-29 00:00:00 (SAT)	2024-06-30 00:00:00 (SUN)	2024-06-29 00:00:00 (SAT)
2024-06-29 15:02:18 (SAT)	2024-06-30 00:00:00 (SUN)	2024-06-30 00:00:00 (SUN)	2024-06-30 00:00:00 (SUN)
2024-06-30 15:02:18 (SUN)	2024-07-01 00:00:00 (MON)	2024-06-30 00:00:00 (SUN)	2024-07-01 00:00:00 (MON)

As Oracle considers the start of the week in the USA to be Sunday.

If you do:

ALTER SESSION SET NLS_TERRITORY = 'Bangladesh'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT dt,
       ROUND(dt)        AS default_result,
       ROUND(dt, 'DAY') AS day_explicit,
       ROUND(dt, 'DD') AS dd_explicit
FROM   table_name;

Then the output is:

DT	DEFAULT_RESULT	DAY_EXPLICIT	DD_EXPLICIT
2024-06-24 15:02:18 (MON)	2024-06-25 00:00:00 (TUE)	2024-06-28 00:00:00 (FRI)	2024-06-25 00:00:00 (TUE)
2024-06-25 15:02:18 (TUE)	2024-06-26 00:00:00 (WED)	2024-06-28 00:00:00 (FRI)	2024-06-26 00:00:00 (WED)
2024-06-26 15:02:18 (WED)	2024-06-27 00:00:00 (THU)	2024-06-28 00:00:00 (FRI)	2024-06-27 00:00:00 (THU)
2024-06-27 15:02:18 (THU)	2024-06-28 00:00:00 (FRI)	2024-06-28 00:00:00 (FRI)	2024-06-28 00:00:00 (FRI)
2024-06-28 15:02:18 (FRI)	2024-06-29 00:00:00 (SAT)	2024-06-28 00:00:00 (FRI)	2024-06-29 00:00:00 (SAT)
2024-06-29 15:02:18 (SAT)	2024-06-30 00:00:00 (SUN)	2024-06-28 00:00:00 (FRI)	2024-06-30 00:00:00 (SUN)
2024-06-30 15:02:18 (SUN)	2024-07-01 00:00:00 (MON)	2024-06-28 00:00:00 (FRI)	2024-07-01 00:00:00 (MON)

As the start of the week is Friday in Bangladesh.

Similarly, if you set the NLS_TERRITORY to a country in the Middle East then you would mostly find that the week starts on Saturday.

Note: If you want to always round to Monday then use the IW format model for the start of the ISO week (as defined by ISO 8601).

fiddle