I have two date variables and I want to present the data in specific manner. So, my query is like this that shows the months name,
SELECT TO_CHAR (ADD_MONTHS (start_date, LEVEL - 1), 'fmMonth') months_namE
FROM (SELECT DATE '2012-01-01' start_date, DATE '2012-03-25' end_date
FROM DUAL)
CONNECT BY LEVEL <=
MONTHS_BETWEEN (TRUNC (end_date, 'MM'),
TRUNC (start_date, 'MM'))
* +1;
and the output is like this,
MONTHS_NAME
-----------
January
February
my intended output is like this
MONTHS_NAME | WEEK | DAYS
-------------------------
January 1 1
January 1 2
January 1 3
January 1 4
January 1 5
January 1 6
January 1 7
January 2 1
January 3
January 4
i tried numerous methods and cant make it work, please help me...
That should be easy using Row Generator method. Rest is only the format mask.
- fmMonth - Month name
- fmDay - Day name
- IW - Week Number
- D - Day Number
In SQL*Plus, my demo is between the dates '2012-01-01' and '2012-02-20':
SQL> def date_start = '2012-01-01'
SQL> def date_end = '2012-02-20'
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, 'fmMonth') month_name,
7 TO_CHAR(date1+LEVEL-1, 'fmDay') day_name,
8 TO_CHAR(date1+LEVEL-1, 'IW') the_week,
9 TO_CHAR(date1+level-1, 'D') the_day
10 FROM data
11 CONNECT BY LEVEL <= date2-date1+1
12 /
old 2: (SELECT to_date('&date_start', 'YYYY-MM-DD') date1,
new 2: (SELECT to_date('2012-01-01', 'YYYY-MM-DD') date1,
old 3: to_date('&date_end', 'YYYY-MM-DD') date2
new 3: to_date('2012-02-20', 'YYYY-MM-DD') date2
Result
MONTH_NAME DAY_NAME THE_WEEK THE_DAY
---------- --------- -------- -------
January Sunday 52 1
January Monday 01 2
January Tuesday 01 3
January Wednesday 01 4
January Thursday 01 5
January Friday 01 6
January Saturday 01 7
January Sunday 01 1
January Monday 02 2
January Tuesday 02 3
January Wednesday 02 4
January Thursday 02 5
January Friday 02 6
January Saturday 02 7
January Sunday 02 1
January Monday 03 2
January Tuesday 03 3
January Wednesday 03 4
January Thursday 03 5
January Friday 03 6
January Saturday 03 7
January Sunday 03 1
January Monday 04 2
January Tuesday 04 3
January Wednesday 04 4
January Thursday 04 5
January Friday 04 6
January Saturday 04 7
January Sunday 04 1
January Monday 05 2
January Tuesday 05 3
February Wednesday 05 4
February Thursday 05 5
February Friday 05 6
February Saturday 05 7
February Sunday 05 1
February Monday 06 2
February Tuesday 06 3
February Wednesday 06 4
February Thursday 06 5
February Friday 06 6
February Saturday 06 7
February Sunday 06 1
February Monday 07 2
February Tuesday 07 3
February Wednesday 07 4
February Thursday 07 5
February Friday 07 6
February Saturday 07 7
February Sunday 07 1
February Monday 08 2
51 rows selected.
SQL>
