有点难度的查询

[碧水幽幽泉]

需求如下:
已知:starttime 和endtime 字段类型均为varchar2(30)类型。
现在假设：starttime 和endtime 的值分别为:2009-12-1 、2009-12-7
要求返回两者之间的日期集，如：
‘2009-12-1，2009-12-2，2009-12-3，2009-12-4，2009-12-5，2009-12-6，2009-12-7’

[碧水幽幽泉]

存储过程或sql语句均可！

[sky711]

SQL> select to_char(to_date('2009-12-1','YYYY-MM-DD') + LEVEL-1,'YYYY-MM-DD') da
tes from dual connect by LEVEl<to_date('2009-12-7','YYYY-MM-DD')-to_date('2009-1
2-1','YYYY-MM-DD')+2;

DATES
----------
2009-12-01
2009-12-02
2009-12-03
2009-12-04
2009-12-05
2009-12-06
2009-12-07

已选择7行。

SQL> select wm_concat(dates) from (select to_char(to_date('2009-12-1','YYYY-MM-D
D') + LEVEL-1,'YYYY-MM-DD') dates from dual connect by LEVEl<to_date('2009-12-7'
,'YYYY-MM-DD')-to_date('2009-12-1','YYYY-MM-DD')+2);

WM_CONCAT(DATES)
--------------------------------------------------------------------------------

2009-12-01,2009-12-02,2009-12-03,2009-12-04,2009-12-05,2009-12-06,2009-12-07

SQL>

[userzh]

select max(substr(sys_connect_by_path(cdate,','),2)) from
(
select to_char(to_date('2009-12-01','yyyy-mm-dd') + level - 1,'yyyy-mm-dd') cdate,level cn,level-1 pn
from dual
connect by level < 10
) connect by PRIOR cn = pn START WITH pn = 0

[userzh]

需求没看清,改了一下

select max(substr(sys_connect_by_path(cdate,','),2)) from
(

select to_char(to_date('2009-12-01','yyyy-mm-dd')/* starttime */ + level - 1,'yyyy-mm-dd') cdate,level cn,level-1 pn
from dual
connect by level <= (to_date('2009-12-07','yyyy-mm-dd')/* endtime */ - to_date('2009-12-01','yyyy-mm-dd')/* starttime */+1)
) connect by PRIOR cn = pn START WITH pn = 0

[碧水幽幽泉]

[Quote=引用 4 楼 userzh 的回复:]
需求没看清,改了一下

select max(substr(sys_connect_by_path(cdate,','),2)) from
(
                             
      select to_char(to_date('2009-12-01','yyyy-mm-dd')/* starttime */ + level - 1,'yyyy-mm-dd') cdate,level cn,level-1 pn
      from dual
      connect by level <= (to_date('2009-12-07','yyyy-mm-dd')/* endtime */ - to_date('2009-12-01','yyyy-mm-dd')/* starttime */+1)
) connect by PRIOR cn = pn START WITH pn = 0
[/Quote]

你的是对的，能解释下你的代码吗？
sys_connect_by_path