Recover all weeks of a month in Oracle SQL

Question

I would like to build a SQL statement to automatically retrieve information about all the weeks of a month. For example, for the month of February 2023:

• Retrieve the first week (Monday to Sunday): 30/01/2023 - 05/01/2023 ...
• Retrieve the last week : 27/02/2023 - 05/03/2023.

Thanks in advance!

Answer 1

Use a row-generator to generate a list of weeks for the month:

WITH input (month) AS (
  SELECT DATE '2023-02-01' FROM DUAL
),
calendar (week_start) AS (
  SELECT TRUNC(TRUNC(month, 'MM'), 'IW') + 7 * (LEVEL - 1)
  FROM   input
  CONNECT BY TRUNC(TRUNC(month, 'MM'), 'IW') + 7 * (LEVEL - 1)
         < ADD_MONTHS(TRUNC(month, 'MM'), 1)
)
SELECT week_start,
       week_start + INTERVAL '6 23:59:59' DAY TO SECOND AS week_end
FROM   calendar

Which outputs:

WEEK_START	WEEK_END
2023-01-30 00:00:00	2023-02-05 23:59:59
2023-02-06 00:00:00	2023-02-12 23:59:59
2023-02-13 00:00:00	2023-02-19 23:59:59
2023-02-20 00:00:00	2023-02-26 23:59:59
2023-02-27 00:00:00	2023-03-05 23:59:59

fiddle