DATE FUNCTION HELP REQUIRED

[uparna]

HI ,
I need to write a function that will give me all the days between two given dates . for example , if i give the function two parameters - 01/01/2001 and 05/01/2001 , then the output should be
01/01/2001
02/01/2001
03/01/2001
04/01/2001
05/01/2001

Is this possible ?

Regards,
Jayaram . Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[sem]

procedure print_dates(pStart in date, pEnd in date)
mDays integer := trunc(pEnd - pStart);
is
begin
for i in 0..mDays loop
dbms_output.put_line(pStart + i);
end loop;
end;

You may also refine printed date format by explicit to_char conversion

 

[uparna]

Hi Sem,
The procedure does not seem to work . I did a few modifications and still the procedure refuses o execute. Here is the procedure :

create or replace procedure print_dates( pStart DATE, pEnd DATE)
is
mDays integer ;
begin
mDays := trunc(TO_DATE(pEnd,'DD/MM/YYYY') - TO_DATE(pStart,'DD/MM/YYYY') );
for i in 0..mDays loop
dbms_output.put_line(TO_DATE(pStart,'DD/MM/YYYY') + i);
end loop;
end;

And here is the error message :

SQL> EXECUTE print_dates('01/06/2001' , '03/06/2001');
BEGIN print_dates('01/06/2001' , '03/06/2001'); END;

*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1


could you guide me on this ?
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[carp]

how about
PROCEDURE date_list(p_low DATE, p_high DATE) IS
BEGIN
FOR i IN to_number(to_char(p_low,'J'))..to_number(to_char(p_high,'J')) LOOP
dbms_output.put_line(to_char(to_date(i,'J'),'DD-Month-YYYY');
END LOOP;
END;

 

[sem]

Of course it must fail to work with such call because it accepts DATE parameters (see declaration). To call it you may either:

perform explicit conversion:
EXECUTE print_dates(to_date('01/06/2001','dd/mm/rrrr') , to_date('03/06/2001','dd/mm/rrrr'))

alter session to accept dates as strings of your preffered format
alter session set nls_date_format = 'dd/mm/rrrr'

pass parameters as strings of your current nls_date_format.