按小时，天，周，月进行查询
按小时统计查询当天存在的所有时刻 即00-23时刻
SELECT sdays + (LEVEL - 1) / 24 dates
FROM (SELECT TO_DATE('2012-01-05' || '00', 'yyyy-MM-dd HH24') sdays,
TO_DATE('2012-01-05' || '23', 'yyyy-MM-dd HH24') edays
FROM dual)
CONNECT BY LEVEL < (edays - sdays) * 24 + 2
按天统计
查询两个日期之间存在的所有天数
SELECT TO_DATE('2012-05-01', 'yyyy-MM-dd') + LEVEL - 1 dates
FROM DUAL
CONNECT BY LEVEL <=
TRUNC(TO_DATE('2012-05-10', 'yyyy-MM-dd') -
TO_DATE('2012-05-01', 'yyyy-MM-dd')) + 1
按周统计
查询两个日期之间存在的所有周
SELECT TRUNC(TO_DATE('2012-01-01', 'yyyy-MM-dd'), 'D') + (LEVEL - 1) * 7 DATES
FROM DUAL
CONNECT BY (LEVEL - 1) * 7 <=
TRUNC(to_date('2012-05-01', 'yyyy-MM-dd'), 'D') -
TRUNC(to_date('2012-01-01', 'yyyy-MM-dd'), 'D')
按月统计
查询两个日期之间的存在的所有月份
SELECT ADD_MONTHS(TRUNC(TO_DATE('2012-04-01', 'yyyy-MM-dd'), 'MONTH'),
LEVEL - 1) dates
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('2012-04-01', 'yyyy-MM-dd'),
'MONTH'),
LEVEL - 1) < = LAST_DAY(TO_DATE('2012-06-01', 'yyyy-MM-dd'))
页:
[1]