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