0

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!

TheoB
  • 1
  • 3
  • Does this answer your question? [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates). Or [Generate a range of dates using SQL](https://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql) – astentx Feb 20 '23 at 08:24

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117