Tuesday, October 28, 2008

SQL Query to print Calendar of any month

Here is an SQL query to print Calendar in the traditional style. Always aligning to 5 rows.

select nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'DD'), null)) SUN ,

nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'DD'), null)) MON ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'DD'), null) TUE ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'DD'), null) WED ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'DD'), null) THU ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'DD'), null) FRI,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'DD'), null) SAT

from dual, (select to_date('01-&Month-&Year','DD-MON-YYYY') MY_DATE from dual)

connect by level<=5

3 comments:

Dilu.. said...

SuuuupppppppeeeeeeeerrrrrrrrrrrrrB

Unknown said...

super da...

but this calender is not true in 1752 september.

Anonymous said...

Good post. I found a simple query here..
http://www.acehints.com/2012/12/oracle-sql-query-to-print-calendar.html
http://www.acehints.com/p/site-map.html