Query to print calender for the year

Query to print calender for the year, AskHareesh blog for Oracle Apps

Query to print calender for the year

SELECT
        LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
        "Tue", "Wed", "Thu", "Fri", "Sat"
        FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
        TO_CHAR (dt + 1, 'iw') week,
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Sun",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Mon",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Tue",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Wed",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Thu",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Fri",
        MAX (DECODE (TO_CHAR (dt, 'd'),
        '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
        )
        ) "Sat"
        FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM
        ALL_OBJECTS
WHERE
        ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y'))
GROUP BY
        TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY
        TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

*/

No comments:

Post a Comment