Days in a month

[nametullah]

Hello

I want to write a query which prints the days in a month. I mean output should be like this:

01-AUG-04
02-AUG-04
..
..
..
..
..
30-AUG-04

In the where condition i can give the name of month and the range of dates.

Thanks
Nametullah kalair

[akkerend]

In 9i you can use a pipelined table function:

SQL> create or replace type date_list as table of date;
2 /

Type created.

SQL>
SQL> create or replace function pipeline_date(start_date in date, number_of_days in number)
2 return date_list pipelined as
3 dt date;
4 i integer:=0;
5 begin
6 while i < number_of_days
7 loop
8 dt:=start_date+i;
9 i:=i+1;
10 pipe row(dt);
11 end loop;
12 return;
13 end;
14 /

Function created.


SQL> select *
2 from table(pipeline_date(trunc(sysdate,'MM'),30));

COLUMN_VA
---------
01-AUG-04
02-AUG-04
03-AUG-04
04-AUG-04
05-AUG-04
06-AUG-04
07-AUG-04
08-AUG-04
09-AUG-04
10-AUG-04
11-AUG-04

COLUMN_VA
---------
12-AUG-04
13-AUG-04
14-AUG-04
15-AUG-04
16-AUG-04
17-AUG-04
18-AUG-04
19-AUG-04
20-AUG-04
21-AUG-04
22-AUG-04

COLUMN_VA
---------
23-AUG-04
24-AUG-04
25-AUG-04
26-AUG-04
27-AUG-04
28-AUG-04
29-AUG-04
30-AUG-04

30 rows selected.

[tamilselvan]

Another method:

select rownum as run_num,
       to_date('15/01/2004','dd/mm/yyyy')+ rownum from 
       (select 1  from dual group by cube (1,1,1,1,1)) ; 

The above SQL outputs 32 days from the given date.
Now you can filter what ever you want.

Tamil

[slimdave]

Tamil gets a special prize for the least intuitive solution

[nametullah]

Thanks a lot for all u people.