Generating Consecutive Dates

Below is technique that I used to generate rows of consecutive dates. I had a report that required labor costs for each day of between two dates. This same technique can be used to generate a particular number of rows. This is one place where you can intentionally use a cartesian product.

Here is an example that can be executed in SQL*Plus

variable p_start_mm varchar2(4)
variable p_start_yyyy varchar2(4)
variable p_end_mm varchar2(4)
variable p_end_yyyy varchar2(4)
exec :p_start_mm := '01'
exec :p_start_yyyy := '2012'
exec :p_end_mm := '04'
exec :p_end_yyyy := '2012'
select to_char(ROWNUM + to_date(:p_start_mm :p_start_yyyy, 'MM-YYYY') - 1, 'DD-MON-YYYY') gen_date from dual connect by level <= last_day(to_date(:p_end_mm :p_end_yyyy, 'MMYYYY')) - to_date(:p_start_mm :p_start_yyyy, 'MMYYYY') + 1;