Pages

Friday, September 23, 2011

Display all the dates of current month by SQL


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