Skip navigation.

Creating a Calendar in a single SQL statement

Ahysanali M. Kadiwala's picture

A few days ago, somebody asked if it is possible to write a Calendar in a single SQL statement.

Here you go...

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

For more queries like this, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14741686777707

Enjoy
E

Wow....ALl the familiar

Wow.... ALl the familiar builtins, still innovative result.
Great! Thank you.

Great work

calender sql using Listagg function.

Undefine inpdate
select ' MONTH YYYY WK Mo Tu We Th Fr Sa Su' Calender from dual
union
select a.w1||listagg(decode(a.dt1,'01',LPAD('01',to_number(decode(a.d1,'1','8',a.d1))*3-4,' '),a.dt1),' ') WITHIN GROUP (ORDER BY a.w1) Calender
from (
Select to_char(trunc(to_date('&&inpdate','dd/mm/yyyy'),'mm')+rownum-1,'d') D1,
to_char(trunc(to_date('&&inpdate','dd/mm/yyyy'),'mm')+rownum-1,'dd') DT1,
to_char(trunc(to_date('&&inpdate','dd/mm/yyyy'),'mm')+rownum-1,'MM-MON IYYY IW ') w1
from dba_tables
where add_months(trunc(to_date('&&inpdate','dd/mm/YYYY'),'YYYY'),12)>trunc(to_date('&&inpdate','dd/mm/YYYY'),'YYYY')+rownum-1) a
group by a.w1;

Visit my site: www.acehints.com

Hi,When I tried to execute

Hi,

When I tried to execute the query I can see all the Saturday days are pushed one row down :(

Should we change the query?

required a small change

Hi,

Its a great job but I guess a small change is required, if you observe the december month..

here is the modified query.

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, 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 dt, (case when dt >=
to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week
from(
SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

hi...

thats damn kewl.... just known functions and one query.. its amazing....

another small change

Truly a GREAT post, nice correction.
I ran into this by accident and ran it today (24/01/2011); I noticed a problem similar to what you saw in December 2008 happen in January 2011.
So I added a small fix, similar to yours, in the case of the first week of January, I needed to change the first week from 52 or 53 to 00 (for some years it was marked as 52 and others 53; I couldn't set the week to 01 because we end up with an 8+ day week, so "00" resolved the problem):
**notice I also changed the "SYSDATE" to a DO_DATE() so people can run this for any year more easily

--- Complete calendar for a year
---- Please replace all occurrences of TO_DATE('2011','YYYY') for the desired year or for SYSDATE
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue","Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, 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 dt, (case when dt >= to_date(to_char(dt, 'dd/')||'12/'||to_char(TO_DATE('2011','YYYY'),'yyyy'),'dd/mm/yyyy') 
                                  and wk = '01' then '53' 
                             when dt <= to_date(to_char(dt, 'dd/')||'01/'||to_char(TO_DATE('2011','YYYY'),'yyyy'),'dd/mm/yyyy') 
                                  and wk in ('52','53') then '00'
                        else wk end
                       ) week
            from (
                  SELECT TRUNC(TO_DATE('2011','YYYY'),'y')-1+ROWNUM dt, TO_CHAR(TRUNC(TO_DATE('2011','YYYY'),'y')-1+ROWNUM+1,'iw') wk
                  FROM all_objects
                  WHERE ROWNUM <= ADD_MONTHS(TRUNC(TO_DATE('2011','YYYY'),'y'),12) - TRUNC(TO_DATE('2011','YYYY'),'y')
                 )
           )
      GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week
     )
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

This is the new query for

This is the new query for English version:

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'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'6',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);

and, for Italian:

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH, "Lun", "Mar",
"Mer", "Gio", "Ven", "Sab","Dom"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Lun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mar",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mer",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Gio",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Ven",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sab",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Dom"
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, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

excellent

Really great man!

Execellent

Great.....

Thanks

Great

Excellent. Thanks. Bring another one ...man.

Great

Its really an excellent learning website. Really wonderful.

Great Job

Ram

Gr8

Its Really Great.......

A Proud Member of this site........

WoW

thanks man

hi

its working and amazing query

Hi The Best You given

Hi
You given the best

Even though One More view From My side

select b.*,
substr(to_char(to_date(a."1"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "1",
substr(to_char(to_date(a."2"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "2",
substr(to_char(to_date(a."3"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "3",
substr(to_char(to_date(a."4"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "4",
substr(to_char(to_date(a."5"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "5",
substr(to_char(to_date(a."6"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "6",
substr(to_char(to_date(a."7"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "7",
substr(to_char(to_date(a."8"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "8",
substr(to_char(to_date(a."9"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "9",
substr(to_char(to_date(a."10"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "10",
substr(to_char(to_date(a."11"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "11",
substr(to_char(to_date(a."12"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "12",
substr(to_char(to_date(a."13"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "13",
substr(to_char(to_date(a."14"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "14",
substr(to_char(to_date(a."15"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "15",
substr(to_char(to_date(a."16"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "16",
substr(to_char(to_date(a."17"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "17",
substr(to_char(to_date(a."18"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "18",
substr(to_char(to_date(a."19"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "19",
substr(to_char(to_date(a."20"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "20",
substr(to_char(to_date(a."21"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "21",
substr(to_char(to_date(a."22"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "22",
substr(to_char(to_date(a."23"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "23",
substr(to_char(to_date(a."24"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "24",
substr(to_char(to_date(a."25"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "25",
substr(to_char(to_date(a."26"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "26",
substr(to_char(to_date(a."27"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "27",
substr(to_char(to_date(a."28"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3) "28",
case when b.seq=2 then
case when &year/4=round(&year/4) then
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end
else
substr(to_char(to_date(a."29"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
end "29",
case when b.seq<>2 then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "30",
case when b.seq in (1,3,5,7,8,10,12) then
substr(to_char(to_date(a."30"||'/'||b.seq||'/'||&year,'dd/mm/yyyy'),'Day'),1,3)
else
'NA'
end "31"
from
(
Select
1 "1",
2 "2",
3 "3",
4 "4",
5 "5",
6 "6",
7 "7",
8 "8",
9 "9",
10 "10",
11 "11",
12 "12",
13 "13",
14 "14",
15 "15",
16 "16",
17 "17",
18 "18",
19 "19",
20 "20",
21 "21",
22 "22",
23 "23",
24 "24",
25 "25",
26 "26",
27 "27",
28 "28",
29 "29",
30 "30",
31 "31"
from dual
)
a,
(
select
rownum seq,
case when rownum=1 then 'January'
else
case when rownum=2 then 'February'
else
case when rownum=3 then 'March'
else
case when rownum=4 then 'April'
else
case when rownum=5 then 'May'
else
case when rownum=6 then 'June'
else
case when rownum=7 then 'July'
else
case when rownum=8 then 'August'
else
case when rownum=9 then 'September'
else
case when rownum=10 then 'October'
else
case when rownum=11 then 'November'
else
'December'
end
end
end
end
end
end
end
end
end
end
end
"Months"
from dual connect by level<=12
)
b

nice one

its really nice query,
but i could see same week being displayed for 30th and 31st for each months.

correct me if i m wrong here

regards
Vidya

I think its been mistakenly

I think its been mistakenly given as "30" in the line 45 of the query.
its working fine when I modified it to "31"

Regards
Vidya

Para español - For Spanish

Hi!

A version for spanish:

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) Mes,TO_NUMBER(week) semana,"Dom","Lun", "Mar", "Mie",
"Jue", "Vie", "Sab"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Lun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mar",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mie",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Jue",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Vie",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sab",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Dom"
FROM ( select dt, (case when dt >=
to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week
from(
SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Great Solution

Great Idea from Great Programmer.

Absolutely Good Job

Grate Job done

Other Years

What about future or prior years? This only picks up the current year.

oww tht is really great....

oww tht is really great....

Pls help me in understanding this calender

i just completed SQL part and started with PL/SQL...My sir gave me mini project titled "calender with SQL"

i saw the query what u wrote...its really amazing....but i have not understood about all_object table?

what exactly it contains of? and weather to create this table before preceeding this query....?

kindly help me in this regard....

wow!!!!!!!!!!

it's really nice...........

Excellent query!

Excellent! Great concept, it brings new concept while quering!

Appriciable:)

Great innovative.

Good work... Great.....

Regards
RajaBaskar

A brand-new query is here.

I posted it in the forum.
(But this version is not international.)
http://www.orafaq.com/forum/t/94953/78939/
and my site.
http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=69

with t as (
select to_date('200811','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)



       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                           1
         2          3          4          5          6          7          8
         9         10         11         12         13         14         15
        16         17         18         19         20         21         22
        23         24         25         26         27         28         29
        30      

calender for 9998 years

SELECT MONTH,"SUN","MON","TUE","WED","THU","FRI","SAT" FROM
(
SELECT TO_CHAR(RW,'MONTH YYYY') MONTH,
TO_CHAR(RW+1,'IW') WEEK,
MAX(DECODE(TO_CHAR(RW,'D'),'1',LPAD(TO_CHAR(RW,'FMDD'),2))) "SUN",
MAX(DECODE(TO_CHAR(RW,'D'),'2',LPAD(TO_CHAR(RW,'FMDD'),2))) "MON",
MAX(DECODE(TO_CHAR(RW,'D'),'3',LPAD(TO_CHAR(RW,'FMDD'),2))) "TUE",
MAX(DECODE(TO_CHAR(RW,'D'),'4',LPAD(TO_CHAR(RW,'FMDD'),2))) "WED",
MAX(DECODE(TO_CHAR(RW,'D'),'5',LPAD(TO_CHAR(RW,'FMDD'),2))) "THU",
MAX(DECODE(TO_CHAR(RW,'D'),'6',LPAD(TO_CHAR(RW,'FMDD'),2))) "FRI",
MAX(DECODE(TO_CHAR(RW,'D'),'7',LPAD(TO_CHAR(RW,'FMDD'),2))) "SAT"
FROM
(
SELECT (TO_DATE('1-JAN-0001','DD-MON-YYYY')-1)+LEVEL RW
FROM DUAL
CONNECT BY
LEVEL<=ADD_MONTHS((TO_DATE('1-JAN-0001','DD-MON-YYYY')),12*9998)-TO_DATE('1-JAN-0001','DD-MON-YYYY')
)
GROUP BY TO_CHAR(RW,'MONTH YYYY'),TO_CHAR(RW+1,'IW')
ORDER BY MONTH,WEEK
)
ORDER BY TO_DATE(MONTH,'MONTH YYYY'),TO_NUMBER(WEEK)
/

SET PAGESIZE 200
BREAK ON MONTH SKIP 1
COL MONTH FOR A18
COL SUN FOR A5
COL MON FOR A5
COL TUE FOR A5
COL WED FOR A5
COL THU FOR A5
COL FRI FOR A5
COL SAT FOR A5