在Oracle SQL中自动获取指定月份的所有周信息

以下是可以自动获取指定月份所有周（周一至周日）信息的Oracle SQL语句，支持传入任意年份和月份：

WITH specified_month AS (
    -- 定义指定的年份和月份，可替换为绑定变量或直接修改数值
    SELECT 
        2023 AS target_year,
        2 AS target_month
    FROM dual
),
month_boundaries AS (
    -- 获取指定月份的第一天和最后一天
    SELECT
        TRUNC(TO_DATE(target_year || '-' || target_month, 'YYYY-MM'), 'MM') AS first_day,
        LAST_DAY(TRUNC(TO_DATE(target_year || '-' || target_month, 'YYYY-MM'), 'MM')) AS last_day
    FROM specified_month
),
week_range AS (
    -- 计算覆盖指定月份的第一个周的周一，以及最后一个周的周日
    SELECT
        -- 找到包含当月第一天的周的周一：如果第一天是周一则不变，否则取前一个周一
        CASE WHEN TO_CHAR(first_day, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MON' 
             THEN first_day 
             ELSE TRUNC(first_day, 'IW') - 7 END AS start_of_first_week,
        -- 找到包含当月最后一天的周的周日：如果最后一天是周日则不变，否则取下一个周日
        CASE WHEN TO_CHAR(last_day, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN' 
             THEN last_day 
             ELSE NEXT_DAY(last_day, 'SUN') END AS end_of_last_week
    FROM month_boundaries
)
SELECT
    -- 周序号：从1开始计数
    ROW_NUMBER() OVER (ORDER BY week_start) AS week_number,
    -- 周起始日期（周一）
    TO_CHAR(week_start, 'DD/MM/YYYY') AS week_start_date,
    -- 周结束日期（周日）
    TO_CHAR(week_end, 'DD/MM/YYYY') AS week_end_date,
    -- 周范围描述
    '第' || ROW_NUMBER() OVER (ORDER BY week_start) || '周（周一至周日）为 ' || TO_CHAR(week_start, 'DD/MM/YYYY') || ' - ' || TO_CHAR(week_end, 'DD/MM/YYYY') AS week_description
FROM (
    -- 生成所有周的起始和结束日期，每次递增7天
    SELECT
        start_of_first_week + (LEVEL - 1) * 7 AS week_start,
        start_of_first_week + LEVEL * 7 - 1 AS week_end
    FROM week_range
    CONNECT BY start_of_first_week + (LEVEL - 1) * 7 <= end_of_last_week
)
ORDER BY week_number;

代码说明 #

1. specified_month 子查询：定义目标年份和月份，可直接修改target_year和target_month的值，或替换为绑定变量（如:p_year和:p_month）用于程序调用。
2. month_boundaries 子查询：通过TRUNC和LAST_DAY函数获取指定月份的第一天和最后一天。
3. week_range 子查询：计算覆盖该月份的所有周的时间范围边界：
   • 起始边界：找到包含当月第一天的周的周一，若当月第一天本身是周一则直接使用，否则取上一个周一。
   • 结束边界：找到包含当月最后一天的周的周日，若当月最后一天是周日则直接使用，否则通过NEXT_DAY函数获取下一个周日。
4. 最终查询：使用CONNECT BY生成所有周的日期序列，通过ROW_NUMBER()生成周序号，并格式化日期输出。

测试结果（2023年2月） #

执行上述SQL后，将返回如下结果：

注意事项 #

• 日期语言设置：使用NLS_DATE_LANGUAGE=ENGLISH确保DY格式返回英文缩写，避免因数据库语言环境不同导致判断错误。
• 周定义调整：若需要将周范围改为周日至周六，只需修改CASE判断条件和NEXT_DAY的参数即可。

内容的提问来源于stack exchange，提问作者TheoB