第一种方法:
SELECT ROWNUM, 开始日期 + ROWNUM - 1 AS SHOW_TIME
FROM (SELECT * FROM DUAL CONNECT BY ROWNUM < 99999)
WHERE ROWNUM < 结束日期 - 开始日期 + 1;
例如查询本月1号到本月最后一天的所有日期:
SELECT ROWNUM, TRUNC(SYSDATE, 'mm') + ROWNUM - 1 AS SHOW_TIME
FROM (SELECT * FROM DUAL CONNECT BY ROWNUM < 99999)
WHERE ROWNUM < TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - TRUNC(SYSDATE, 'mm') + 1;
==========================================================================
第二种方法:(推荐,效率高)
SELECT ROWNUM, 开始日期 + ROWNUM - 1 AS SHOW_TIME
FROM DUAL
CONNECT BY ROWNUM < 结束日期 - 开始日期 + 1;
例如查询本月1号到本月最后一天的所有日期:
SELECT ROWNUM, TRUNC(SYSDATE, 'MM') + ROWNUM - 1 AS SHOW_TIME
FROM DUAL
CONNECT BY ROWNUM < TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - TRUNC(SYSDATE, 'MM') + 1;
例如查询2016-07-10到2016-07-16的所有日期:
SELECT ROWNUM, TRUNC(to_Date('2016-07-10','yyyy-mm-dd'), 'dd') + ROWNUM - 1 AS SHOW_TIME
FROM DUAL
CONNECT BY ROWNUM < TRUNC(to_Date('2016-07-16','yyyy-mm-dd'), 'dd') - TRUNC(to_Date('2016-07-10','yyyy-mm-dd'), 'dd') + 1;