My World A Blog On All The Things That Happen In My Life

Query For Displaying The Calender of The Current Year using SQL
about Computing on 24/06/2008 - like it?

Another of things I got via IpMessenger, this query(yes, its a QUERY, not even a full-blown code) returns the calender of the current year. Do note this query will work only in Oracle.

Here’s the 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,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);

A screenshot of part of the result returned

[ad]

Related posts:

1. The Power and Simplicity of Oracle Hierarchical Queries They say “Necessity is the Power of Invention” – a...
2. 1 Year At Chip Forums Today I completed an anniversary of sorts – 1 year...

Tags: calender, oracle, query, SQL

Thats very cool man!

People seem to be having a lot of free time at the office. atleast they are doing something related to sql, so thats good

Lovingly written by Reeteshinator on 24/06/2008

I have no idea on PL/SQL. But my fiancee saw this and felt its pretty cool. She was asking if this is out of google or own coding…

Lovingly written by Ashwin S on 25/06/2008

@Ashwin Hehe that’s obvious isnt it, from where its taken

Lovingly written by Sathya on 25/06/2008

Hehe

Lovingly written by Ashwin S on 25/06/2008

thank you, dude

Lovingly written by invoissikirl on 25/09/2008

thats for sure, dude

Lovingly written by Irrillnournom on 29/09/2008