-- Oracle Date Query to get month day, date and week number
-- for any year month in yyyymm format

WITH q_ymth AS
 (SELECT  &i_yyyymm  i_yyyymm  FROM DUAL),
q_val1 AS
 (SELECT 
   i_yyyymm, DECODE(LENGTH(i_yyyymm),6,i_yyyymm,NULL) the_date 
  FROM q_ymth),
q_val2 AS
 (SELECT 
   i_yyyymm,
   DECODE(GREATEST(TO_NUMBER(SUBSTR(i_yyyymm,5)),13),13,
    TO_DATE(i_yyyymm,'yyyymm'),NULL) the_date
  FROM q_val1
  WHERE the_date IS NOT NULL),
q_tab AS
 (SELECT i_yyyymm, the_date  FROM q_val2
  WHERE the_date IS NOT NULL)
SELECT
 i_yyyymm Yr_month,
 level month_day,
 TO_CHAR(the_date+level-1,'mm/dd/yyyy') calendar_day,
 TO_CHAR(TRUNC(the_date,'MON')+level-1,'ww') calendar_week 
FROM q_tab
WHERE  the_date IS NOT NULL
CONNECT BY level < ROUND(last_day(the_date) - the_date+1)+1;

-- Oracle Date Summary Query to get week number, start date,  
-- end date and days of the week for any year month in yyyymm 
-- format

WITH q_ymth AS
 (SELECT  &i_yyyymm  i_yyyymm  FROM DUAL),
q_val1 AS
 (SELECT 
   i_yyyymm, DECODE(LENGTH(i_yyyymm),6,i_yyyymm,NULL) the_date
  FROM q_ymth),
q_val2 AS
 (SELECT 
   i_yyyymm,
   DECODE(GREATEST(TO_NUMBER(SUBSTR(i_yyyymm,5)),13),13,
    TO_DATE(i_yyyymm,'yyyymm'),NULL) the_date
  FROM q_val1
  WHERE the_date IS NOT NULL),
q_tab AS
 (SELECT i_yyyymm, the_date  FROM q_val2
  WHERE the_date IS NOT NULL)
SELECT
 i_yyyymm Yr_month,
 TO_NUMBER(TO_CHAR(TRUNC(the_date,'MON')+level-1,'ww')) calendar_week, 
 TO_CHAR(MIN(the_date+level-1),'mm/dd/yyyy') wk_start_date,
 TO_CHAR(MAX(the_date+level-1),'mm/dd/yyyy') wk_end_date,
 MAX(the_date+level) - MIN(the_date+level-1) days
FROM q_tab
WHERE  the_date IS NOT NULL
CONNECT BY level < ROUND(last_day(the_date) - the_date+1)+1
GROUP BY
 i_yyyymm, TO_CHAR(TRUNC(the_date,'MON')+level-1,'ww')
ORDER BY 2;

-- Oracle Date Query to get start date, end date for a given week 
-- in the current year

WITH w_date AS
 (SELECT &i_wk wk_no FROM DUAL),
w_valid AS
 (SELECT DECODE(SIGN(TO_CHAR(LAST_DAY(TO_DATE(12||
                     TO_CHAR(SYSDATE,'yyyy'),'mmyyyy')),'ww')-wk_no),
   0, wk_no, 1, wk_no, NULL) wk_no
  FROM w_date)
SELECT
 wk_no week_no,
 TO_CHAR(TRUNC(SYSDATE,'yyyy')+((wk_no-1)*7),'mm/dd/yyyy')
   wk_start_date,
 TO_CHAR(TRUNC(SYSDATE,'yyyy')+(wk_no*7)-1,'mm/dd/yyyy')
   wk_end_date
FROM w_valid;