Search code examples
sqloracle-databasedaterounding

Oracle 23c ROUND with DATE data type

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? enter image description here

Solution

  • 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