Tags
calendar, day name, day number, list of dates, month name, oracle date generator, row generator, week number
This is a frequently asked question in most of the forums. Recently saw such question in Stack Overflow Generate Month Name, Day name, Week number and Day number between two dates. Typical questions are like “How to generate a list of dates between two dates“. Or, “How to generate the months, Week Number and Days between two dates“.
This could be achieved with a simple hierarchical query and proper datetime format mask. Such query is usually known as Row Generator method.
Let’s say we have two dates, 1st Jan 2015 and 20th Feb 2015 as start date and end date respectively. The requirement is to generate a list as shown in the below demonstration:
In SQL*Plus,
SQL> def date_start = '2015-01-01'
SQL> def date_end = '2015-02-20'
SQL>
SQL> COLUMN the_date FORMAT A11
SQL> COLUMN month_name FORMAT A10
SQL> COLUMN day_name FORMAT A10
SQL> COLUMN the_week FORMAT A8
SQL> COLUMN the_day FORMAT A7
SQL>
SQL> WITH DATA AS
2 (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
3 to_date('&date_end', 'YYYY-MM-DD') date2
4 FROM dual
5 )
6 SELECT TO_CHAR(date1+LEVEL-1, 'DD-MON-YYYY') the_date,
7 TO_CHAR(date1+LEVEL-1, 'fmMonth') month_name,
8 TO_CHAR(date1+LEVEL-1, 'fmDay') day_name,
9 TO_CHAR(date1+LEVEL-1, 'IW') the_week,
10 TO_CHAR(date1+level-1, 'D') the_day
11 FROM data
12 CONNECT BY LEVEL <= date2-date1+1
13 /
old 2: (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
new 2: (SELECT to_date('2015-01-01', 'YYYY-MM-DD') date1,
old 3: to_date('&date_end', 'YYYY-MM-DD') date2
new 3: to_date('2015-02-20', 'YYYY-MM-DD') date2
THE_DATE MONTH_NAME DAY_NAME THE_WEEK THE_DAY
----------- ---------- ---------- -------- -------
01-JAN-2015 January Thursday 01 5
02-JAN-2015 January Friday 01 6
03-JAN-2015 January Saturday 01 7
04-JAN-2015 January Sunday 01 1
05-JAN-2015 January Monday 02 2
06-JAN-2015 January Tuesday 02 3
07-JAN-2015 January Wednesday 02 4
08-JAN-2015 January Thursday 02 5
09-JAN-2015 January Friday 02 6
10-JAN-2015 January Saturday 02 7
11-JAN-2015 January Sunday 02 1
12-JAN-2015 January Monday 03 2
13-JAN-2015 January Tuesday 03 3
14-JAN-2015 January Wednesday 03 4
15-JAN-2015 January Thursday 03 5
16-JAN-2015 January Friday 03 6
17-JAN-2015 January Saturday 03 7
18-JAN-2015 January Sunday 03 1
19-JAN-2015 January Monday 04 2
20-JAN-2015 January Tuesday 04 3
21-JAN-2015 January Wednesday 04 4
22-JAN-2015 January Thursday 04 5
23-JAN-2015 January Friday 04 6
24-JAN-2015 January Saturday 04 7
25-JAN-2015 January Sunday 04 1
26-JAN-2015 January Monday 05 2
27-JAN-2015 January Tuesday 05 3
28-JAN-2015 January Wednesday 05 4
29-JAN-2015 January Thursday 05 5
30-JAN-2015 January Friday 05 6
31-JAN-2015 January Saturday 05 7
01-FEB-2015 February Sunday 05 1
02-FEB-2015 February Monday 06 2
03-FEB-2015 February Tuesday 06 3
04-FEB-2015 February Wednesday 06 4
05-FEB-2015 February Thursday 06 5
06-FEB-2015 February Friday 06 6
07-FEB-2015 February Saturday 06 7
08-FEB-2015 February Sunday 06 1
09-FEB-2015 February Monday 07 2
10-FEB-2015 February Tuesday 07 3
11-FEB-2015 February Wednesday 07 4
12-FEB-2015 February Thursday 07 5
13-FEB-2015 February Friday 07 6
14-FEB-2015 February Saturday 07 7
15-FEB-2015 February Sunday 07 1
16-FEB-2015 February Monday 08 2
17-FEB-2015 February Tuesday 08 3
18-FEB-2015 February Wednesday 08 4
19-FEB-2015 February Thursday 08 5
20-FEB-2015 February Friday 08 6
51 rows selected.In the above demo, the format used for week number is ‘IW’. If you want the week number to change for each month, then use ‘W`:
SQL> def date_start = '2015-01-01'
SQL> def date_end = '2015-02-20'
SQL>
SQL> COLUMN the_date FORMAT A11
SQL> COLUMN month_name FORMAT A10
SQL> COLUMN day_name FORMAT A10
SQL> COLUMN the_week FORMAT A8
SQL> COLUMN the_day FORMAT A7
SQL>
SQL> WITH DATA AS
2 (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
3 to_date('&date_end', 'YYYY-MM-DD') date2
4 FROM dual
5 )
6 SELECT TO_CHAR(date1+LEVEL-1, 'DD-MON-YYYY') the_date,
7 TO_CHAR(date1+LEVEL-1, 'fmMonth') month_name,
8 TO_CHAR(date1+LEVEL-1, 'fmDay') day_name,
9 TO_CHAR(date1+LEVEL-1, 'W') the_week,
10 TO_CHAR(date1+level-1, 'D') the_day
11 FROM data
12 CONNECT BY LEVEL <= date2-date1+1
13 /
old 2: (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
new 2: (SELECT to_date('2015-01-01', 'YYYY-MM-DD') date1,
old 3: to_date('&date_end', 'YYYY-MM-DD') date2
new 3: to_date('2015-02-20', 'YYYY-MM-DD') date2
THE_DATE MONTH_NAME DAY_NAME THE_WEEK THE_DAY
----------- ---------- ---------- -------- -------
01-JAN-2015 January Thursday 1 5
02-JAN-2015 January Friday 1 6
03-JAN-2015 January Saturday 1 7
04-JAN-2015 January Sunday 1 1
05-JAN-2015 January Monday 1 2
06-JAN-2015 January Tuesday 1 3
07-JAN-2015 January Wednesday 1 4
08-JAN-2015 January Thursday 2 5
09-JAN-2015 January Friday 2 6
10-JAN-2015 January Saturday 2 7
11-JAN-2015 January Sunday 2 1
12-JAN-2015 January Monday 2 2
13-JAN-2015 January Tuesday 2 3
14-JAN-2015 January Wednesday 2 4
15-JAN-2015 January Thursday 3 5
16-JAN-2015 January Friday 3 6
17-JAN-2015 January Saturday 3 7
18-JAN-2015 January Sunday 3 1
19-JAN-2015 January Monday 3 2
20-JAN-2015 January Tuesday 3 3
21-JAN-2015 January Wednesday 3 4
22-JAN-2015 January Thursday 4 5
23-JAN-2015 January Friday 4 6
24-JAN-2015 January Saturday 4 7
25-JAN-2015 January Sunday 4 1
26-JAN-2015 January Monday 4 2
27-JAN-2015 January Tuesday 4 3
28-JAN-2015 January Wednesday 4 4
29-JAN-2015 January Thursday 5 5
30-JAN-2015 January Friday 5 6
31-JAN-2015 January Saturday 5 7
01-FEB-2015 February Sunday 1 1
02-FEB-2015 February Monday 1 2
03-FEB-2015 February Tuesday 1 3
04-FEB-2015 February Wednesday 1 4
05-FEB-2015 February Thursday 1 5
06-FEB-2015 February Friday 1 6
07-FEB-2015 February Saturday 1 7
08-FEB-2015 February Sunday 2 1
09-FEB-2015 February Monday 2 2
10-FEB-2015 February Tuesday 2 3
11-FEB-2015 February Wednesday 2 4
12-FEB-2015 February Thursday 2 5
13-FEB-2015 February Friday 2 6
14-FEB-2015 February Saturday 2 7
15-FEB-2015 February Sunday 3 1
16-FEB-2015 February Monday 3 2
17-FEB-2015 February Tuesday 3 3
18-FEB-2015 February Wednesday 3 4
19-FEB-2015 February Thursday 3 5
20-FEB-2015 February Friday 3 6
51 rows selected.
SQL>
Hope it helps!
Lalit Kumar B 