This post shows how to create a calendar view using pure SQL.
E.g. September 2016 should look like this:
WEEK MON TUE WED THU FRI SAT SUN
---- --- --- --- --- --- --- ---
35 01 02 03 04
36 05 06 07 08 09 10 11
37 12 13 14 15 16 17 18
38 19 20 21 22 23 24 25
39 26 27 28 29 30
To create this kind of calendar you can use recursive queries (aka connect by queries), to create a list of all days belonging to the current month.
SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
FROM dual
CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE);
Having this list of all days the only additional thing we have to consider is how to group and format this data. I have choosen a DECODE approach to group the dates to the appropriate weekday column.
WITH days_of_curr_month AS (SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
FROM dual
CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE))
,data_formatter AS (SELECT TO_CHAR(theDate,'IYYY') AS Iyear
,TO_CHAR(theDate,'IW') AS Iweek
,TO_CHAR(theDate,'DY','NLS_DATE_LANGUAGE=AMERICAN') AS MyDateDay
,TO_CHAR(theDate,'DD') AS MyDate
FROM days_of_curr_month)
SELECT IWeek AS Week
,MAX(DECODE(MyDateDay,'MON',MyDate)) AS MON
,MAX(DECODE(MyDateDay,'TUE',MyDate)) AS TUE
,MAX(DECODE(MyDateDay,'WED',MyDate)) AS WED
,MAX(DECODE(MyDateDay,'THU',MyDate)) AS THU
,MAX(DECODE(MyDateDay,'FRI',MyDate)) AS FRI
,MAX(DECODE(MyDateDay,'SAT',MyDate)) AS SAT
,MAX(DECODE(MyDateDay,'SUN',MyDate)) AS SUN
FROM data_formatter
GROUP BY IYear ,Iweek
ORDER BY IYear ,IWeek;
The IYear information is needed as in the ISO-Calendar dates like January 1 – January 3 or December 29 – December 31 may belong to the previous or the following calendar year (week 53 or 1) and we have to make sure, that week 1 of the following year is placed at the end of the list and week 53 of the previous year is placed on top.