SELECT mon_date, week_no, TO_CHAR(mon_date,'D') day, TO_NUMBER(TO_CHAR(mon_date,'DD')) dd
FROM (
SELECT curr_mon + LEVEL - 1 mon_date,
( TRUNC (curr_mon + LEVEL - 1, 'D')
- TRUNC (TRUNC (curr_mon + LEVEL - 1, 'Y'), 'D')
) / 7 + 1 week_no
FROM (SELECT TO_DATE (TO_CHAR(SYSDATE,'YYYYMM'), 'YYYYMM') curr_mon
FROM DUAL)
CONNECT BY curr_mon + LEVEL - 1 <= LAST_DAY (curr_mon));
No comments:
Post a Comment