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';
WITH RCTE (THE_DATE) AS (
SELECT TRUNC(SYSDATE, 'IW')
FROM DUAL
UNION ALL
SELECT THE_DATE + 7
FROM RCTE
WHERE THE_DATE < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1
)
SELECT THE_DATE,
TO_CHAR(THE_DATE, 'YYYY') AS YYYY,
TO_CHAR(THE_DATE, 'D') AS D,
(THE_DATE - TRUNC(TRUNC(THE_DATE + INTERVAL '6' DAY, 'MM'), 'IW'))/7 + 1 AS W,
TO_CHAR(THE_DATE + INTERVAL '6' DAY, 'MM') AS MM,
TO_CHAR(THE_DATE, 'IYYY') AS IYYY,
TO_CHAR(THE_DATE, 'IW') AS IW
FROM RCTE
ORDER BY THE_DATE;
| THE_DATE | YYYY | D | W | MM | IYYY | IW |
|---|---|---|---|---|---|---|
| 2025-03-31 00:00:00 | 2025 | 1 | 1 | 04 | 2025 | 14 |
| 2025-04-07 00:00:00 | 2025 | 1 | 2 | 04 | 2025 | 15 |
| 2025-04-14 00:00:00 | 2025 | 1 | 3 | 04 | 2025 | 16 |
| 2025-04-21 00:00:00 | 2025 | 1 | 4 | 04 | 2025 | 17 |
| 2025-04-28 00:00:00 | 2025 | 1 | 1 | 05 | 2025 | 18 |
| 2025-05-05 00:00:00 | 2025 | 1 | 2 | 05 | 2025 | 19 |
| 2025-05-12 00:00:00 | 2025 | 1 | 3 | 05 | 2025 | 20 |
| 2025-05-19 00:00:00 | 2025 | 1 | 4 | 05 | 2025 | 21 |
| 2025-05-26 00:00:00 | 2025 | 1 | 1 | 06 | 2025 | 22 |
| 2025-06-02 00:00:00 | 2025 | 1 | 2 | 06 | 2025 | 23 |
| 2025-06-09 00:00:00 | 2025 | 1 | 3 | 06 | 2025 | 24 |
| 2025-06-16 00:00:00 | 2025 | 1 | 4 | 06 | 2025 | 25 |
| 2025-06-23 00:00:00 | 2025 | 1 | 5 | 06 | 2025 | 26 |
| 2025-06-30 00:00:00 | 2025 | 1 | 1 | 07 | 2025 | 27 |
| 2025-07-07 00:00:00 | 2025 | 1 | 2 | 07 | 2025 | 28 |
| 2025-07-14 00:00:00 | 2025 | 1 | 3 | 07 | 2025 | 29 |
| 2025-07-21 00:00:00 | 2025 | 1 | 4 | 07 | 2025 | 30 |
| 2025-07-28 00:00:00 | 2025 | 1 | 1 | 08 | 2025 | 31 |
| 2025-08-04 00:00:00 | 2025 | 1 | 2 | 08 | 2025 | 32 |
| 2025-08-11 00:00:00 | 2025 | 1 | 3 | 08 | 2025 | 33 |
| 2025-08-18 00:00:00 | 2025 | 1 | 4 | 08 | 2025 | 34 |
| 2025-08-25 00:00:00 | 2025 | 1 | 5 | 08 | 2025 | 35 |
| 2025-09-01 00:00:00 | 2025 | 1 | 1 | 09 | 2025 | 36 |
| 2025-09-08 00:00:00 | 2025 | 1 | 2 | 09 | 2025 | 37 |
| 2025-09-15 00:00:00 | 2025 | 1 | 3 | 09 | 2025 | 38 |
| 2025-09-22 00:00:00 | 2025 | 1 | 4 | 09 | 2025 | 39 |
| 2025-09-29 00:00:00 | 2025 | 1 | 1 | 10 | 2025 | 40 |
| 2025-10-06 00:00:00 | 2025 | 1 | 2 | 10 | 2025 | 41 |
| 2025-10-13 00:00:00 | 2025 | 1 | 3 | 10 | 2025 | 42 |
| 2025-10-20 00:00:00 | 2025 | 1 | 4 | 10 | 2025 | 43 |
| 2025-10-27 00:00:00 | 2025 | 1 | 1 | 11 | 2025 | 44 |
| 2025-11-03 00:00:00 | 2025 | 1 | 2 | 11 | 2025 | 45 |
| 2025-11-10 00:00:00 | 2025 | 1 | 3 | 11 | 2025 | 46 |
| 2025-11-17 00:00:00 | 2025 | 1 | 4 | 11 | 2025 | 47 |
| 2025-11-24 00:00:00 | 2025 | 1 | 5 | 11 | 2025 | 48 |
| 2025-12-01 00:00:00 | 2025 | 1 | 1 | 12 | 2025 | 49 |
| 2025-12-08 00:00:00 | 2025 | 1 | 2 | 12 | 2025 | 50 |
| 2025-12-15 00:00:00 | 2025 | 1 | 3 | 12 | 2025 | 51 |
| 2025-12-22 00:00:00 | 2025 | 1 | 4 | 12 | 2025 | 52 |
| 2025-12-29 00:00:00 | 2025 | 1 | 1 | 01 | 2026 | 01 |
| 2026-01-05 00:00:00 | 2026 | 1 | 2 | 01 | 2026 | 02 |