如何用MySQL按周一为起始拆分月份为周并判断日期所属周

一、拆分指定月份为以周一为起始的周列表 #

要将指定月份拆分为以周一为起始的周（允许周跨月），可以使用MySQL的递归CTE实现，自动生成包含week_name和weekStartDate的结果集：

-- 替换这里的'2023-08-01'为目标月份的任意日期即可
SET @target_month = '2023-08-01';

WITH RECURSIVE month_weeks AS (
    -- 初始化：找到目标月份对应的第一个周一（可能属于上月）
    SELECT
        DATE_SUB(
            DATE_FORMAT(@target_month, '%Y-%m-01'),
            INTERVAL WEEKDAY(DATE_FORMAT(@target_month, '%Y-%m-01')) DAY
        ) AS weekStartDate,
        1 AS week_num
    UNION ALL
    -- 递归生成后续周
    SELECT
        DATE_ADD(weekStartDate, INTERVAL 7 DAY),
        week_num + 1
    FROM month_weeks
    -- 终止条件：下一周的起始日期月份不超过目标月份
    WHERE MONTH(DATE_ADD(weekStartDate, INTERVAL 7 DAY)) <= MONTH(@target_month)
)
-- 输出最终结果
SELECT
    CONCAT('week-', week_num) AS week_name,
    weekStartDate,
    DATE_ADD(weekStartDate, INTERVAL 6 DAY) AS weekEndDate
FROM month_weeks
ORDER BY weekStartDate;

逻辑说明 #

1. DATE_FORMAT(@target_month, '%Y-%m-01') 获取目标月份的第一天
2. WEEKDAY() 返回日期的周内索引（0=周一，6=周日），用DATE_SUB减去对应天数得到最近的周一（即该月第一周的起始）
3. 递归CTE每次给当前周起始日加7天，生成下一周，直到下一周的起始日月份超出目标月份时停止
4. 最后拼接week_name并输出周的起止日期

二、查询任意日期所属的月内周数 #

传入任意日期，通过以下SQL直接返回该日期在所属月份的周编号（如week-1、week-2）：

-- 替换这里的'2023-08-05'为要查询的日期
SET @target_date = '2023-08-05';

SELECT
    CONCAT('week-', 
        FLOOR(
            DATEDIFF(
                @target_date,
                -- 找到目标日期所属月份的第一个周一
                DATE_SUB(
                    DATE_FORMAT(@target_date, '%Y-%m-01'),
                    INTERVAL WEEKDAY(DATE_FORMAT(@target_date, '%Y-%m-01')) DAY
                )
            ) / 7
        ) + 1
    ) AS week_name;

逻辑说明 #

1. 先找到目标日期所属月份的第一个周一（逻辑同第一部分）
2. 用DATEDIFF计算目标日期与该周一的天数差，除以7取整后加1，得到该日期在月内的周序号
3. 拼接成要求的week-N格式输出，适配所有月份

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