Creating a Calendar in a single SQL statement

Ahysanali M. Kadiwala's picture
Submitted by Ahysanali M. Ka... on Tue, 2008-06-10 09:45
articles: 
SQL & PL/SQL

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

»

Comments

Submitted by jramya on Wed, 2008-06-11 06:15.

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

»

Submitted by shonymt on Thu, 2012-12-06 01:08.

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

»

Submitted by jramya on Wed, 2008-06-11 06:41.

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?

»

Submitted by venkatbollu on Tue, 2008-06-17 01:38.

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

»

Submitted by z_ashwini on Thu, 2009-04-09 06:01.

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

»

Submitted by LFLFM on Mon, 2011-01-24 06:56.

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

»

Submitted by aghirotti on Thu, 2008-06-12 02:04.

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

»

Submitted by eoracle on Thu, 2008-06-12 03:59.

Really great man!

»

Submitted by ukumarjsr2 on Tue, 2008-06-17 06:58.

Great.....

Thanks

»

Submitted by moysekou on Mon, 2008-06-23 06:30.

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

»

Submitted by umar79 on Mon, 2008-06-23 09:09.

Its really an excellent learning website. Really wonderful.

»

Submitted by rammi on Mon, 2008-06-23 21:52.

Ram

»

Submitted by oracleapps_sagar on Wed, 2008-06-25 00:53.

Its Really Great.......

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

»

Submitted by amdabd on Thu, 2008-06-26 02:49.

thanks man

»

Submitted by chand.sarat on Tue, 2008-07-01 23:20.

its working and amazing query

»

Submitted by mm_kanish05 on Wed, 2008-07-02 22:35.

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

»

Submitted by atozvs on Wed, 2008-08-06 07:27.

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

»

Submitted by atozvs on Wed, 2008-08-06 07:36.

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

»

Submitted by rodrcort on Wed, 2008-08-06 10:25.

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

»

Submitted by m_golam_hossain on Tue, 2008-08-26 00:55.

Great Idea from Great Programmer.

»

Submitted by nishchaljunghare on Tue, 2008-09-09 08:27.

Grate Job done

»

Submitted by sjm133 on Tue, 2008-09-09 11:39.

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

»

Submitted by savlon99 on Sun, 2008-09-14 02:19.

oww tht is really great....

»

Submitted by new_sri on Thu, 2008-09-18 01:06.

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....

»

Submitted by ramachandra_oracle on Sun, 2008-09-21 04:21.

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

»

Submitted by rajat_chaudhary on Tue, 2008-11-11 01:34.

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

Appriciable:)

»

Submitted by rajabaskar on Wed, 2008-11-19 00:48.

Good work... Great.....

Regards
RajaBaskar

»

Submitted by zozogirl on Sat, 2008-11-22 20:42.

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

»

Submitted by mastansky on Sun, 2013-07-28 04:23.

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

»