输入日期范围,获取日期范围内所有的日期
SqlServer写法:
Select Convert(Varchar(10), DateAdd(dd, ID, Cast('2014-08-01' As DateTime)), 120) as rlDate
From (SELECT TOP 40 ID=number FROM MASTER..SPT_VALUES WHERE TYPE='P' ) DT
where cast(Convert(Varchar(10), DateAdd(dd, ID, Cast('2014-08-01' As DateTime)), 120) as datetime)<=Cast('2014-08-30' As DateTime)
返回结果集:
rlDate
2014-08-01
2014-08-02
2014-08-03
...
2014-08-30
那么在Oracle中怎实现这个呢, Oracle又没有MASTER..SPT_VALUES 辅助
那么替代方式已经找到了
select (to_date('2014-08-01','yyyy-mm-dd')+rownum-1) rn from dual connect by rownum <= (to_date('2014-08-30','yyyy-mm-dd')-to_date('2014-08-01','yyyy-mm-dd')+1)
返回结果集也是
2014-08-01
2014-08-02
2014-08-03
...
2014-08-30