--小时列表
SELECT to_date('2013-07-01 12', 'yyyy-mm-dd hh24') + (ROWNUM - 1) / 24 sdate FROM dual
CONNECT BY ROWNUM <= (to_date('2013-07-02 22', 'yyyy-mm-dd hh24') - to_date('2013-07-01 12', 'yyyy-mm-dd hh24')) * 24 + 1;
--天列表
SELECT to_char(daylist,'yyyy-MM-dd') daylist from(SELECT TO_CHAR(TO_DATE('2020-07-01', 'yyyy-MM-dd') + ROWNUM - 1, 'yyyy-MM-dd') as daylist
FROM DUAL CONNECT BY ROWNUM <=trunc(to_date('2020-07-06', 'yyyy-MM-dd') -to_date('2020-07-01', 'yyyy-MM-dd')) + 1);
--月份列表
SELECT to_char(add_months(to_date('2013-01', 'yyyy-mm'), ROWNUM - 1), 'yyyy-mm') day_id FROM dual
CONNECT BY ROWNUM <= months_between(to_date('2013-07', 'yyyy-mm'), to_date('2013-01', 'yyyy-mm')) + 1;
--年份列表
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), (ROWNUM - 1) * 12),'yyyy') as yearlist FROM DUAL
CONNECT BY ROWNUM <=months_between(to_date('2015-06', 'yyyy-MM'),to_date('2014-10', 'yyyy-MM')) / 12 + 2;
Oracle CONNECT BY 的用法(在一段时间内生成连续的日期,小时,天,月,年)
最新推荐文章于 2022-11-24 11:17:56 发布