SQL FOR CALENDAR OUTPUT

SELECT TO_DATE ('2008-01', 'yyyy-mm') - 1 + LEVEL AS daterange
FROM DUAL
CONNECT BY LEVEL <= 366 ---------------------------------------------------------------------------------------------

select to_date('&date','MM-YYYY')-1 + level as DateRange
from dual
where (to_date('&date','MM-YYYY')-1+level) <= last_day(to_date('&Date','MM-YYYY')) connect by level<=31; ---------------------------------------------------------------------------------------------

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); ---------------------------------------------------------------------------------------------

with t as (
select to_date('200905','yyyymm') ym from dual)
SELECT * FROM (
SELECT MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '1', LEVEL)) SUN
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '2', LEVEL)) MON
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '3', LEVEL)) TUE
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '4', LEVEL)) WED
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '5', LEVEL)) THU
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '6', LEVEL)) FRI
, MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '7', LEVEL)) SAT
FROM T
CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1 GROUP BY TRUNC (YM + LEVEL, 'iw') ORDER BY 7) ---------------------------------------------------------------------------------------------