I want the sql to get the start of the week date and end of the week date for a given date and my week starts from saturday and ends with friday, but towards the month end I want it ends with the last day of the calendar month. EG for 29th and 30th June given date the WK_END_DTE should be 30/06/2020
I got this far
WITH REF_DATE AS
(
SELECT to_date('01-MAY-2020','dd-mon-yyyy') AS from_date
, to_date('30-JUN-2020','dd-mon-yyyy') AS to_date
FROM dual
)
SELECT from_date + LEVEL - 1 as dte
,TRUNC ( from_date + LEVEL - 1
, 'IW'
) - 2 AS wk_start_dte
, TRUNC ( from_date + LEVEL - 1
, 'IW'
) + 4 AS wk_end_dte
FROM REF_DATE
CONNECT BY LEVEL <= 1 + to_date - from_date
;