一、最近遇到需求需要查询一段时间的所有日期,作为基础表外联业务表来展现记录,记录每天的数据情况,研究之后做下记录
1、查询当前月份所有日期:
SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));
2、查询近1个月所有日期:
SELECT trunc(add_months(sysdate, -1)) + ROWNUM - 1 as daylist
FROM DUAL
CONNECT BY ROWNUM <= trunc(sysdate) - trunc(add_months(sysdate, -1));
3、查询近1年所有月份:
SELECT to_char(add_months(SYSDATE, ROWNUM), 'yyyy-mm')
FROM dual
CONNECT BY ROWNUM <= 12;