connected by months

Ok, I'm new using this connect by thing. But its always quite useful. I have this small problem you guys might be able to help me...

Given start month (say to_char(sysdate,'YYYYMM')) and end month (say, to_char(add_months(sysdate, 6),'YYYYMM')), want to get the list of months in between, in the same format.

Well, I want to use this into a partitions automation script. My best shot so far (pretty pitiful) yields invalid months e.g.'201034'... (and yea, I know, incredibly inefficient)

Follows the code:

SELECT id
from
开发者_如何转开发    (select to_char(add_months(sysdate, 6),'YYYYMM') as tn_end, to_char(sysdate,'YYYYMM') as tn_start from dual) tabla,
    (select * from
        (Select Level as Id from dual connect by Level <= (Select to_char(add_months(sysdate, 1),'YYYYMM')from dual)) where id > to_char(sysdate,'YYYYMM')) t
Where 
    t.Id between tabla.tn_start and tabla.tn_end

how do I do to make this query return only valid months? Any tips?

cheers mates,

f.

---

Best way might be to separate out the row generator from the date function. So generate a list from 0 to 6 and calculate months from that. If you want to pass the months in then do that in the with clause

with my_counter as (
  Select Level-1 as id 
  from dual 
  connect by Level <= 7
) 
select to_char(add_months(sysdate, id),'YYYYMM') from my_counter

The example below will allow you to plug in the dates you require to work out the difference.

with my_counter as (
  Select Level-1 as id 
  from dual 
  connect by level <= months_between(add_months(trunc(sysdate,'MM'), 6), 
      trunc(sysdate,'MM')) + 1
) 
select to_char(add_months(trunc(sysdate, 'MM'), id),'YYYYMM') from my_counter

---

For generating dates and date ranges, I strongly suggest you create a permanent calendar table with one row for each day. Even if you keep 20 years in this table it will be small ~7500 rows. Having such a table lets you attach additional (potentially non-standard) information to a date. For example your company may use a 6-week reporting period which you cannot extract using TO_CHAR / TO_DATE. Pre-compute it and store it in this table.

Oh, and Oracle 11g has automatic partition management. If you are stuck with 10g, then this article may be of interest to you? Automatic Partition Management for Oracle 10g

---

Try this:

with numbers as
( select level as n from dual
  connect by level <= 7
)
select to_char (add_months (trunc(sysdate,'MM'), n-1), 'YYYYMM') id
from numbers;

ID
------
201012
201101
201102
201103
201104
201105
201106