This content has been marked as final. Show 3 replies
The below will give you ranges
If you want something else, please provide sample data(CREATE TABLE and INSERT statements), expected output and your db version..
with dates as ( select to_date('01-01-2013','dd-mm-yyyy') from_dt, to_date('20-01-2013','dd-mm-yyyy') to_dt from dual ) select from_dt+(level-1)*7 from_dt, least(from_dt+(level*7)-1,to_dt) to_dt from dates connect by from_dt+(level-1)*7 <= to_dt; FROM_DT TO_DT --------- --------- 01-JAN-13 07-JAN-13 08-JAN-13 14-JAN-13 15-JAN-13 20-JAN-13
Edited by: jeneesh on Feb 18, 2013 5:38 PM
Is this what you looking for?
WITH T AS
(select to_date('01/01/2013', 'MM/DD/YYYY') FROM_DT, to_date('01/20/2013', 'MM/DD/YYYY') TO_DT from dual)
SELECT --FROM_DT, TO_DT, LEVEL M_WEEK,
FROM_DT + ((LEVEL - 1) * 7) NEW_FROM,
LEAST(FROM_DT + ((LEVEL) * 7), TO_DT ) NEW_TO
CONNECT BY LEVEL <= CEIL((TO_DT - FROM_DT)/7)
If no post some sample data and desired output.
Thanks for your valuable suggestions.
Now I am able to get the required results.