参考网址:
http://www.orafaq.com/forum/mv/msg/95011/493456/102589/#msg_493456
http://www.orafaq.com/forum/t/184746/170570/
1、构造两个日期之间的时间:
可以采用以下两种方式来实现:
(1):
1 SELECT to_date('2012-10-04', 'yyyy-mm-dd') - 1 + LEVEL 2 FROM dual 3 CONNECT BY LEVEL <= to_date('2012-11-04', 'yyyy-mm-dd') - 4 to_date('2012-10-04', 'yyyy-mm-dd') + 1;
(2):
1 WITH data AS 2 (SELECT to_date('2012-10-04', 'yyyy-mm-dd') date1, 3 to_date('2012-11-04', 'yyyy-mm-dd') date2 4 FROM dual) 5 SELECT to_char(date1 + LEVEL - 1, 'yyyy-mm-dd') the_date 6 FROM data 7 CONNECT BY LEVEL <= date2 - date1 + 1
2、获取当年所有的日期:
关于trunc和round的日期参数的格式请参考:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
1 SELECT trunc(SYSDATE, 'YEAR') + LEVEL - 1 the_date 2 FROM dual 3 CONNECT BY LEVEL <= 4 trunc(add_months(SYSDATE, 12), 'YEAR') - trunc(SYSDATE, 'YEAR')
3、获取当月的所以日期:
1 SELECT trunc(SYSDATE, 'MONTH') + LEVEL - 1 the_date 2 FROM dual 3 CONNECT BY LEVEL <= last_day(SYSDATE) - trunc(SYSDATE, 'MONTH') + 1
4、获取当前周的日期:使用ISO格式(从周一开始,周末结束);
1 SELECT TRUNC(SYSDATE,'IW') + LEVEL - 1 THE_DATE 2 FROM DUAL 3 CONNECT BY LEVEL <= 7
5、获取当前日期的前六个月:
1 SELECT trunc(add_months(SYSDATE, -1 * LEVEL), 'month') MONTH 2 FROM dual 3 CONNECT BY LEVEL <= 6
6、