Generate Month Name, Day name, Week number and Day

Question

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...

Answer 1

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>