1、列出日期
已知開始日期和結束日期,如何用一條SQL得出這個範圍内的所有日期。
eg:
已知:
2007/04/25 2007/05/02
得出:
2007/04/25
2007/04/26
2007/04/27
2007/04/28
2007/04/29
2007/04/30
2007/05/01
2007/05/02
SQL为:
SELECT TO_CHAR(to_date('2007/04/25', 'yyyy/mm/dd') + (level - 1), 'yyyy/mm/dd')
FROM dual
CONNECT BY TRUNC(to_date('2007/04/25', 'yyyy/mm/dd')) + level - 1 <= TRUNC(to_date('2007/05/02', 'yyyy/mm/dd'));
2、列出月份
已知:2011-03 2012-07
得出:
2011-03
2011-04
2011-05
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
2011-12
2012-01
2012-02
2012-03
2012-04
2012-05
2012-06
SQL为:
SELECT TO_CHAR( add_months(to_date('2011-03', 'yyyy-mm'), ROWNUM - 1), 'YYYY-MM' ) AS yearmonth
FROM DUAL
CONNECT BY ROWNUM <=
(SELECT months_between(to_date('2012-07', 'yyyy-mm'), to_date('2011-03', 'yyyy-mm'))
FROM dual
)
参考资料
[1].求助:列出日期範圍内的所有日期. http://www.itpub.net/thread-763220-1-1.html
[2].列出指定时间范围内的所有月份.http://www.dedecms.com/knowledge/data-base/oracle/2012/1228/18330.html