Search code examples
sqloracledaterecursive-query

Row for each date from start date to end date

What I'm trying to do is take a record that looks like this:

 Start_DT    End_DT     ID
4/5/2013    4/9/2013    1

and change it to look like this:

    DT      ID
4/5/2013    1
4/6/2013    1
4/7/2013    1
4/8/2013    1
4/9/2013    1

it can be done in Python but I am not sure if it is possible with SQL Oracle? I am having difficult time making this work. Any help would be appreciated.

Thanks

Solution

  • connect by level is useful for these problems. suppose the first CTE named "table_DT" is your table name so you can use the select statement after that.

    with table_DT as (
    select 
        to_date('4/5/2013','mm/dd/yyyy') as Start_DT, 
        to_date('4/9/2013', 'mm/dd/yyyy') as End_DT, 
        1 as ID
    from dual
)
select 
    Start_DT + (level-1) as DT, 
    ID
from table_DT
connect by level <= End_DT - Start_DT +1
;