connect by level !!

Every day teaches you something new,,, only thing is, you should keep your eyes open and ears alert.

While working on INC today, got something new like below - which I had never thought of.

select to_date(startDate,'dd-mon-yyyy') - 1 + level
from dual
connect by level <= (to_date(endDate,'dd-mon-yyyy')+1) - to_date(startDate,'dd-mon-yyyy')

Scenario was, start date and end date are 2 IN parameters for my proc. Using those, they were generating date ranges. Lets say, you gave start date as 1-Nov-2011 and end date as 10-Nov-2011, So the query will generate 10 rows with 1st to 10 Nov as data.

As in image, query will generate the result.

How will it work?

Seems, there is a keyword connect by level just as connect by prior.

select  level
from    dual
connect by level <= 10 ;

When you fire above statement, it will 

generate records with 1 to 10 numbers.

Same logic they have used in generating Date Range series. Level they have creating 

by subtracting End Date and Start Date and simply added level to start date. As 

keeps on increase at every iteration, date gets increase along with it.

You can get more information at google, but following like I like most

http://www.sqlsnippets.com/en/topic-11821.html  

They query you can use for date series preparation is

select to_date('1-Nov-2011','dd-mon-yyyy') -1 + level 
from dual
connect by level <= (to_date('9-Nov-2011','dd-mon-yyyy')+1) - to_date('1-Nov-2011','dd-mon-yyyy')