你可以使用递归查询和LAST_DAY函数：

WITH Ranges (start_date, end_date) AS (
  SELECT DATE'2023-10-16', DATE '2023-11-13' FROM DUAL
),
weeks (week_first_date, week_last_date, end_date, sl_no) AS (
  SELECT TRUNC(start_date),
         LEAST(
           TRUNC(start_date) + INTERVAL '6 23:59:59' DAY TO SECOND,
           LAST_DAY(TRUNC(start_date) + INTERVAL '23:59:59' HOUR TO SECOND),
           end_date
         ),
         end_date,
         1
  FROM   ranges
UNION ALL
  SELECT week_last_date + INTERVAL '1' SECOND,
         LEAST(
           week_last_date + INTERVAL '7' DAY,
           LAST_DAY(week_last_date + INTERVAL '1' DAY),
           end_date
         ),
         end_date,
         sl_no + 1
  FROM   weeks
  WHERE  week_last_date < end_date
)
SELECT week_first_date,
       week_last_date,
       sl_no
FROM   weeks;

字符串
其输出：
| 第一周日期|星期_最后_日期|SL_NO|
| --|--|--|
| 2023-10-16 00：00：00| 2023-10-22 23：59：59| 1 |
| 2023-10-23 00：00：00| 2023-10-29 23：59：59| 2 |
| 2023年10月30日00：00：00| 2023-10-31 23：59：59| 3 |
| 2023年11月1日00：00：00| 2023-11-07 23：59：59| 4 |
| 2023年11月8日00：00：00| 2023年11月13日00：00：00| 5 |
fiddle

WITH DateRanges AS (
    SELECT
        TO_DATE('2023-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') START_DATE,
        TO_DATE('2023-11-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS') END_DATE
    FROM
        DUAL
),
RecursiveRanges (WEEK_FIRST_DATE, WEEK_LAST_DATE, SL_NO, NEXT_MONTH_START) AS (
    SELECT
        GREATEST(START_DATE, TRUNC(START_DATE + 7 * (LEVEL - 1))),
        LEAST(END_DATE, TRUNC(START_DATE + 7 * (LEVEL - 1)) + 7 - INTERVAL '1' SECOND),
        LEVEL,
        LEAD(TRUNC(START_DATE + 7 * LEVEL), 1, NULL) OVER (ORDER BY LEVEL) AS NEXT_MONTH_START
    FROM
        DateRanges
    CONNECT BY
        PRIOR WEEK_LAST_DATE + 1 = WEEK_FIRST_DATE
        AND PRIOR SYS_GUID() IS NOT NULL
        AND LEVEL <= CEIL((END_DATE - START_DATE + 1) / 7)
    START WITH
        START_DATE = (SELECT MIN(START_DATE) FROM DateRanges)
)
SELECT
    WEEK_FIRST_DATE,
    CASE
        WHEN WEEK_LAST_DATE > NEXT_MONTH_START - 1 THEN NEXT_MONTH_START - INTERVAL '1' SECOND
        ELSE WEEK_LAST_DATE
    END AS WEEK_LAST_DATE,
    SL_NO
FROM
    RecursiveRanges;

字符串