SELECT TRUNC(to_DATE('2022/8/29', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 1 AS 周一,
TRUNC(to_DATE('2022/8/29', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 7 AS 周日,
TO_CHAR(to_DATE('2022/8/29', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'IW') AS 第几周
FROM DUAL
CONNECT BY ROWNUM <=
TRUNC(to_DATE('2022/10/2', 'YYYY-MM-DD') -
to_DATE('2022/8/29', 'YYYY-MM-DD')) / 7 + 1
获取某个时间段之间的月份列表如下:
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2022-06', 'YYYY-MM'), ROWNUM - 1),
'YYYY-MM') DAY_ID
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date('2022-10', 'yyyy-mm'),
to_date('2022-06', 'yyyy-mm')) + 1