I'm using Oracle 12c. I need to generate dates for the start and end of weeks which begin on Thursday and end the following Wednesday. An example of the output I'd like is -

I have the following SQL to generate the Start Date(s) -

SELECT startdate
  FROM (SELECT next_day(date '2020-03-12' - 1, 'Thursday')   (level - 1) * 7 AS startdate
          FROM dual
       CONNECT BY level <=
                   ((date'2024-03-31' - next_day(date '2020-03-12' - 1, 'Wednesday')   7) / 7))

and this for End Dates -

(SELECT enddate
   FROM (SELECT next_day(date '2020-03-12' - 1, 'Wednesday')   (level - 1) * 7 as enddate
           FROM dual
         CONNECT BY level <= ((date'2024-03-31' - next_day(date'2020-03-12' - 1, 'Thursday')   7) / 7)))

Is it even possible to combine these in a single SQL query so the output of the query matches the desired format?

If so, then the addition of the week number would also be rather nice...:)

CodePudding user response：

Generate the start date and then add 6 days to get the end date:

SELECT startdate,
       startdate   INTERVAL '6' DAY AS enddate,
       week
FROM   (
  SELECT NEXT_DAY(date'2020-03-12' - 1, 'Thursday')
             ( level - 1 ) * INTERVAL '7' DAY as startdate,
         LEVEL AS week
  FROM   DUAL
  CONNECT BY
         NEXT_DAY(date'2020-03-12' - 1, 'Thursday')
             ( level - 1 ) * INTERVAL '7' DAY
             INTERVAL '6' DAY
           <= date'2024-03-31'
)

Which outputs:

STARTDATE	ENDDATE	WEEK
2020-03-12 00:00:00	2020-03-18 00:00:00	1
2020-03-19 00:00:00	2020-03-25 00:00:00	2
2020-03-26 00:00:00	2020-04-01 00:00:00	3
...	...	...
2024-03-07 00:00:00	2024-03-13 00:00:00	209
2024-03-14 00:00:00	2024-03-20 00:00:00	210
2024-03-21 00:00:00	2024-03-27 00:00:00	211

db<>fiddle here