I'm trying to return all the individual weeks of the year, with the respective number of the week and number of the week respective to the month.

This query does that but how can I alter the logic to be that if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month ex: this current week started on 3/30/25 the query considers that week 5 or march whereas I need it to be week 1 on April

Heres the query I'm using FIDDLE

Also posted below, thanks for any assistance.

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,
  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

I'm trying to return all the individual weeks of the year, with the respective number of the week and number of the week respective to the month.

This query does that but how can I alter the logic to be that if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month ex: this current week started on 3/30/25 the query considers that week 5 or march whereas I need it to be week 1 on April

Heres the query I'm using FIDDLE

Also posted below, thanks for any assistance.

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,
  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

• sql
• oracle-database
• oracle11g

• Please edit the question and provide minimal reproducible example with the expected output for your query. – MT0 Commented 11 hours ago
• "if a month ends and a new one begins that week is considered week 1 of the new month rather than week 4/5 of the current month" Note: that is not the same logic as defined for ISO dates - with ISO dates the week is in a year, or month, if the majority of the week is in that year, or month (i.e. which year/month contains the middle day, Thursday, of the week). – MT0 Commented 11 hours ago

1 Answer 1

If the month has changed during the week then the Sunday of that week must be in the new month; therefore, you can find the start of the new month by:

• adding 6 days to the date to get to Sunday of that week;
• then truncating that date to the start of the month; then
• find truncate, again, to the start of the ISO week to take you back to the Monday of the week containing the first day of the month; finally
• to get the number of the week of the month, subtract the Monday of the ISO-week containing the start of the month from the original date and divide by 7 to get the number of weeks difference (and add 1 to get your expected value).

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;

Which outputs:

fiddle

Note: If you want to use the same logic as IYYY and IW for the week of month and month columns then don't add 6 days to check the Sunday, instead add 4 days to check which month the Thursday of that week falls into.