I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL.
You can see the question and the answer here
But I thought it might be worth explaining the process. Of course, the way I approach the problem might be totally different to the way others would, and that’s fine. But for the novice, you might get something useful out of this.
So here is January 2016.
The first thing I’m thinking to print a calendar is
- I will need to know the number of days in the month
- I will need to know what day the month starts on
- I will need to segment the data into weeks
So let’s tackle them.
Number of days in the month
I can use LAST_DAY for that.
SQL> select last_day(date '2016-01-16') end_of_month from dual;
END_OF_MO
---------
31-JAN-16
SQL>
SQL> select to_char(last_day(date '2016-01-16'),'DD') days_in_month from dual;
DA
--
31
and because I will be printing 31 days, I will need to generate 31 days worth of rows. Taking the above, I can use the familiar ‘connect by’ trick to generate my date
SQL> select rownum d
2 from dual
3 connect by level <= to_number(to_char(last_day(date '2016-01-16'),'DD'));
D
----------
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
31 rows selected.
So there are my days, now I just need to jiggle them around so they look like a calendar 
Next I need to know when the month starts. First I truncate the date down to the month level
SQL> select trunc(date '2016-01-16','MM') mth_start from dual;
MTH_START
---------
01-JAN-16
and then I want to know what day of the week it commences on
SQL> select to_number(to_char(trunc(date '2016-01-16','MM'),'D')) starts_on from dual;
STARTS_ON
----------
6
My first attempt at working out a week number is then simply to divide by 7 and truncate. I’ve just substituted the explicit first of month literal here for simplicity. We’ll put the full expression from above back in later.
SQL> select date '2016-01-01'+ rownum -1 dy,
2 trunc((rownum-1+to_number(to_char(date '2016-01-01','D')))/7) week_num
3 from dual
4 connect by level <= 31
5 /
DY WEEK_NUM
--------- ----------
01-JAN-16 0
02-JAN-16 1
03-JAN-16 1
04-JAN-16 1
05-JAN-16 1
06-JAN-16 1
07-JAN-16 1
08-JAN-16 1
09-JAN-16 2
10-JAN-16 2
11-JAN-16 2
...
Now I compare that to my true calendar – I see that the 3rd starts on a Sunday, whereas my basic trunc would have the 2nd being Sunday. So I need to offset things by 1, and try again
SQL> select date '2016-01-01'+ rownum -1 dy,
2 trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
3 from dual
4 connect by level <= 31;
DY WEEK_NUM
--------- ----------
01-JAN-16 0
02-JAN-16 0
03-JAN-16 1
04-JAN-16 1
05-JAN-16 1
06-JAN-16 1
07-JAN-16 1
08-JAN-16 1
09-JAN-16 1
10-JAN-16 2
11-JAN-16 2
12-JAN-16 2
13-JAN-16 2
...
That looks better. Now I can also add in the day of the week
SQL> select date '2016-01-01'+ rownum -1 dy,
2 trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
3 to_char(trunc(date '2016-01-01','MM') -1 + rownum,'D') dow
4 from dual
5 connect by level <= 31;
DY WEEK_NUM D
--------- ---------- -
01-JAN-16 0 6
02-JAN-16 0 7
03-JAN-16 1 1
04-JAN-16 1 2
05-JAN-16 1 3
06-JAN-16 1 4
07-JAN-16 1 5
08-JAN-16 1 6
09-JAN-16 1 7
10-JAN-16 2 1
11-JAN-16 2 2
12-JAN-16 2 3
And we are pretty close to being complete. We can now use PIVOT or the common DECODE expression to flip the rows into columns. And hence we arrive at this
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(date '2016-01-01','D')) p
12 ,to_char(date '2016-01-01' -1 + rownum,'D') dow
13 from dual
14 connect by level <= 31
15 )
16 group by trunc(p/7)
17 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
Finally, we put back our original expressions so that we can accept any date of the month, and work from that. Hence our complete solution becomes
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
and we’re done. I hope you found this useful.
Learning is not a spectator sport 
Got some thoughts? Leave a comment