生成多个年(yyyy)
脚本:
SELECT (to_number(to_char(SYSDate, ‘YYYY’)) - 20 + ROWNUM) AS ny
FROM DUAL
CONNECT BY ROWNUM <= 82 order by ny
结果如下:
生成指定年份的所有(yyyy-mm)格式的年月
脚本:select to_char(to_date(dt,‘yyyy-mm’),‘yyyy-mm’) 月份 from
(
SELECT ‘2020’||’-’||to_char(1 + LEVEL - 1) dt
FROM DUAL CONNECT BY LEVEL <= 12
)
查询结果图:
生成指定月份的所有天:
脚本:
with
tdt1 as
(Select to_char(to_date(’${dtm}’,‘yyyy-MM’),‘yyyy-MM’)||’-01’ dt1 from dual)
SELECT TO_CHAR(TO_DATE((select dt1 from tdt1), ‘YYYY-MM-DD’) + ROWNUM - 1,‘YYYY-MM-dd’) 日期
FROM DUAL
CONNECT BY ROWNUM <= (add_months(TO_DATE((select dt1 from tdt1), ‘YYYY-MM-dd’),1) - TO_DATE((select dt1 from tdt1), ‘YYYY-MM-DD’))
结果如下: