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';
SELECT TO_DATE( TO_CHAR(dt, 'YYYY') || '-01-01', 'YYYY-MM-DD' )
+ INTERVAL '7' DAY * (TO_CHAR(dt, 'WW') - 1) AS week_start,
TRUNC(dt, 'WW') AS trunc_week
FROM (
SELECT TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH24:MI:SS') AS dt
FROM DUAL
);
| WEEK_START | TRUNC_WEEK |
|---|---|
| 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
SELECT TRUNC( TO_DATE( TO_CHAR(dt, 'IYYY') || '-01-04', 'YYYY-MM-DD' ), 'IW')
+ INTERVAL '7' DAY * (TO_CHAR(dt, 'IW') - 1) AS iso_week_start,
TRUNC(dt, 'IW') AS trunc_iso_week
FROM (
SELECT TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH24:MI:SS') AS dt
FROM DUAL
);
| ISO_WEEK_START | TRUNC_ISO_WEEK |
|---|---|
| 1969-12-29 00:00:00 | 1969-12-29 00:00:00 |