Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 20, 2016 - 4:14 am UTC

Last updated: February 22, 2016 - 12:24 am UTC

Version: 11.0

Viewed 1000+ times

You Asked

1)Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat).

su mo tu wed thu fri sat
1 2 3 4 5 6 7
.
.
.
......................30


Help me.

and we said...

SQL> define my_date = 12-JAN-16
SQL>
SQL> select
  2   max(decode(dow,1,d,null)) Sun,
  3   max(decode(dow,2,d,null)) Mon,
  4   max(decode(dow,3,d,null)) Tue,
  5   max(decode(dow,4,d,null)) Wed,
  6   max(decode(dow,5,d,null)) Thu,
  7   max(decode(dow,6,d,null)) Fri,
  8   max(decode(dow,7,d,null)) Sat
  9  from
 10   ( select rownum d
 11            ,rownum-2+to_number(to_char(trunc(to_date('&my_date'),'MM'),'D')) p
 12            ,to_char(trunc(to_date('&my_date'),'MM') -1 + rownum,'D') dow
 13     from dual
 14     connect by level <=
 15        to_number(to_char(last_day(to_date('&my_date')),'DD'))
 16    )
 17  group by trunc(p/7)
 18  order by trunc(p/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
        31


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

With Pivots

Rajeshwaran, Jeyabal, February 21, 2016 - 8:22 am UTC

rajesh@ORA11G> select *
  2  from (
  3  select trunc(sysdate,'mm') + level -1  dts,
  4      to_number(to_char( trunc(sysdate,'mm') + level -1 ,'D')) dy,
  5      decode(to_char( trunc(sysdate,'mm') + level -1 ,'DY'),'SUN',
  6          to_char( trunc(sysdate,'mm') + level -1 ,'IW')+1,
  7          to_char( trunc(sysdate,'mm') + level -1 ,'IW')) x
  8  from dual
  9  connect by level <= last_day(sysdate) - trunc(sysdate,'mm') + 1
 10       )
 11  pivot
 12  ( max(dts)
 13    for dy in (1 as sun,2 as mon,3 as tue,4 as wed,
 14        5 as Thur,6 as Fri,7 as Sat) )
 15  order by x
 16  /

         X SUN         MON         TUE         WED         THUR        FRI         SAT
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
         5             01-FEB-2016 02-FEB-2016 03-FEB-2016 04-FEB-2016 05-FEB-2016 06-FEB-2016
         6 07-FEB-2016 08-FEB-2016 09-FEB-2016 10-FEB-2016 11-FEB-2016 12-FEB-2016 13-FEB-2016
         7 14-FEB-2016 15-FEB-2016 16-FEB-2016 17-FEB-2016 18-FEB-2016 19-FEB-2016 20-FEB-2016
         8 21-FEB-2016 22-FEB-2016 23-FEB-2016 24-FEB-2016 25-FEB-2016 26-FEB-2016 27-FEB-2016
         9 28-FEB-2016 29-FEB-2016

5 rows selected.

rajesh@ORA11G>

extract

Nimish Garg, February 21, 2016 - 1:40 pm UTC

I like to use
extract(day from last_day(sysdate))

instead of
to_number(to_char(sysdate,'DD'))


However I have never checked performance
Connor McDonald
February 22, 2016 - 12:24 am UTC

Good point, and probably easier for the next person to read and maintain