Girish's Blog

Friday, August 12, 2011

Oracle Query to produce UNIX cal command like calendar


break on month skip 1
set linesize 20
column month 20
select lpad( Month, 20-(20-length(month))/2 ) month,
       "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
  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))) "Su",
       max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
       max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
       max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
       max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
       max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
       max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
  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)
Posted by Girish at 2:13 AM
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest

No comments:

Post a Comment

Newer Post Older Post Home
Subscribe to: Post Comments (Atom)

Followers

Blog Archive

  • ►  2017 (1)
    • ►  July (1)
  • ►  2012 (3)
    • ►  June (1)
    • ►  January (2)
  • ▼  2011 (5)
    • ►  December (2)
    • ►  November (1)
    • ▼  August (2)
      • Table Sizing Information
      • Oracle Query to produce UNIX cal command like cale...
  • ►  2010 (1)
    • ►  December (1)

About Me

Girish
View my complete profile
Picture Window theme. Theme images by Zemdega. Powered by Blogger.