So I know I can write a query like this to see the current year, day of week, the week number on the year and current month number.
SELECT TO_CHAR (SYSDATE, 'YYYY'),to_char(sysdate, 'd'),to_char(sysdate, 'IW'),to_char(sysdate, 'MM ')
FROM DUAL
How can I expand on that to show that same data but for all the remaining weeks in the year? ex:
(2025/4/03/01)
(2025/4/04/01)
(2025/4/05/01)
You can use a hierarchical query with CONNECT BY to keep adding days until you reach the last day of the current year:
SELECT THE_DATE,
TO_CHAR(THE_DATE, 'YYYY') AS YYYY,
TO_CHAR(THE_DATE, 'D') AS D,
TO_CHAR(THE_DATE, 'W') AS W,
TO_CHAR(THE_DATE, 'MM') AS MM,
TO_CHAR(THE_DATE, 'IYYY') AS IYYY,
TO_CHAR(THE_DATE, 'IW') AS IW
FROM (
SELECT TRUNC(SYSDATE) + LEVEL - 1 AS THE_DATE
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE)
)
ORDER BY THE_DATE
| THE_DATE | YYYY | D | W | MM | IYYY | IW |
|---|---|---|---|---|---|---|
| 2025-01-15 | 2025 | 3 | 3 | 01 | 2025 | 03 |
| 2025-01-16 | 2025 | 4 | 3 | 01 | 2025 | 03 |
| 2025-01-17 | 2025 | 5 | 3 | 01 | 2025 | 03 |
| 2025-01-18 | 2025 | 6 | 3 | 01 | 2025 | 03 |
| 2025-01-19 | 2025 | 7 | 3 | 01 | 2025 | 03 |
| 2025-01-20 | 2025 | 1 | 3 | 01 | 2025 | 04 |
| ... | ||||||
| 2025-12-27 | 2025 | 6 | 4 | 12 | 2025 | 52 |
| 2025-12-28 | 2025 | 7 | 4 | 12 | 2025 | 52 |
| 2025-12-29 | 2025 | 1 | 5 | 12 | 2026 | 01 |
| 2025-12-30 | 2025 | 2 | 5 | 12 | 2026 | 01 |
| 2025-12-31 | 2025 | 3 | 5 | 12 | 2026 | 01 |
Or (preferably, in my opinion anyway) use recursive subquery factoring:
WITH RCTE (THE_DATE) AS (
SELECT TRUNC(SYSDATE)
FROM DUAL
UNION ALL
SELECT THE_DATE + 1
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,
TO_CHAR(THE_DATE, 'W') AS W,
TO_CHAR(THE_DATE, '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-01-15 | 2025 | 3 | 3 | 01 | 2025 | 03 |
| 2025-01-16 | 2025 | 4 | 3 | 01 | 2025 | 03 |
| 2025-01-17 | 2025 | 5 | 3 | 01 | 2025 | 03 |
| 2025-01-18 | 2025 | 6 | 3 | 01 | 2025 | 03 |
| 2025-01-19 | 2025 | 7 | 3 | 01 | 2025 | 03 |
| 2025-01-20 | 2025 | 1 | 3 | 01 | 2025 | 04 |
| ... | ||||||
| 2025-12-28 | 2025 | 7 | 4 | 12 | 2025 | 52 |
| 2025-12-29 | 2025 | 1 | 5 | 12 | 2026 | 01 |
| 2025-12-30 | 2025 | 2 | 5 | 12 | 2026 | 01 |
| 2025-12-31 | 2025 | 3 | 5 | 12 | 2026 | 01 |
The anchor branch gets today's date; the recursive branch adds a day, until you get to the last day of the year.
In both cases I've included the relevant date for reference, and ordered by that, but you might not need it. I also noticed that you're mixing 'normal' and ISO data elements, so I've taken the liberty of showing both YYYY and W values, and the ISO IYYY and IW equivalents. You can use whichever you actually need, of course.
Also note that the D element is NLS-sensitive, so it will produce different values if run in say the UK and USA, depending on whether weeks are taken to start on Sunday or Monday.
(If you only want to see one entry per week you can truncate and/or adjust the initial date to, say, Monday, then add 7 days each time instead of 1 day...)