Query for Display Calander.. SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa" FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH, CASE WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%' AND TO_CHAR (dt + 1, 'iw') = '01' THEN '53' WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%' AND TO_CHAR (dt + 1, 'iw') = '53' THEN '.5' ELSE TO_CHAR (dt + 1, 'iw') END week, MAX (DECODE (TO_CHAR (dt, 'd'), '1', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Su", MAX (DECODE (TO_CHAR (dt, 'd'), '2', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Mo", MAX (DECODE (TO_CHAR (dt, 'd'), '3', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Tu", MAX (DECODE (TO_CHAR (dt, 'd'), '4', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "We", MAX (DECODE (TO_CHAR (dt, 'd'), '5', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Th", MAX (DECODE (TO_CHAR (dt, 'd'), '6', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Fr", MAX (DECODE (TO_CHAR (dt, 'd'), '7', LPAD (TO_CHAR (dt, 'fmdd'), 2) ) ) "Sa" 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_CHAR (DT + 1, 'iw')) ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week) ;
Wednesday, August 8, 2012
Query for Display Calander.. in Oracle
Subscribe to:
Post Comments (Atom)
Hi Yonus,
ReplyDeleteThanks for ur Query