Oralcesql creates calendar information for a specified period of time
The creation start time is 2010 and the end time is 2100.
Create table temp_calendar as select
Min (to_char (day, 'yyyymm') month,
To_char (min (decode (weekday, 1, day), 'dd') w_7,
To_char (min (decode (weekday, 2, day), 'dd') w_1,
To_char (min (decode (weekday, 3, day), 'dd') w_2,
To_char (min (decode (weekday, 4, day), 'dd') w_3,
To_char (min (decode (weekday, 5, day), 'dd') w_4,
To_char (min (decode (weekday, 6, day), 'dd') w_5,
To_char (min (decode (weekday, 7, day), 'dd') w_6
From (select trunc (day, 'yyyy') year,
Day,
Month,
Decode (sign (rn-weekday), 1, week + 1, week) week,
Weekday,
Rn
From (select day,
To_char (day, 'mm') month,
To_char (day, 'w') week,
To_char (day, 'D') weekday,
Row_number () over (partition by to_char (day, 'mm'), to_char (day, 'w') order by day) rn
From (select trunc (to_date ('20170101', 'yyyy'), 'yyyy') + level-1 day
From dual
Connect by rownum <= trunc (to_date ('20140901', 'yyyy'), 'yyyy')-trunc (to_date ('20140901', 'yyyy '), 'yyyy')
Group by a. year, a. month, a. week
Order by a. year, a. month, a. week;