Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
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;
7 rows affected

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;


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)


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;


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)


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;


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)

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)

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)