需求如下:
已知: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’
已知: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> 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-07SQL>
(
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
(
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
你的是对的,能解释下你的代码吗?
sys_connect_by_path