如何修改SQL Server中一年第一周的判定规则为周一启始的完整周

由于SQL Server的ISO_WEEK规则是包含周四的周为当年第一周，不符合你要求的「以周一开头的完整周为第一周」的规则，我们可以通过自定义计算逻辑实现需求：当1月1日不是周一，1月1日所在周属于上一年的最后一周；当年第一周从第一个属于本年的周一开始计算。

核心实现逻辑 #

1. 先设置周一为一周的起始日（避免会话默认设置干扰）：SET DATEFIRST 1;
2. 计算当年第一个完整周的周一：如果1月1日是周一，直接用1月1日；否则取1月1日后的第一个周一。
3. 判断目标日期是否在当年第一个完整周之前：如果是，返回上一年的最后一周周数；否则计算从第一个完整周开始的周数。

验证查询（匹配你的测试用例） #

SET DATEFIRST 1;

WITH YearDates AS (
    SELECT '2020' AS [year], CAST('2020-01-01' AS DATE) AS [date], 53 AS expected_week
    UNION ALL SELECT '2021' AS [year], CAST('2021-01-01' AS DATE) AS [date], 53 AS expected_week
    UNION ALL SELECT '2022' AS [year], CAST('2022-01-01' AS DATE) AS [date], 52 AS expected_week
    UNION ALL SELECT '2023' AS [year], CAST('2023-01-01' AS DATE) AS [date], 52 AS expected_week
    UNION ALL SELECT '2024' AS [year], CAST('2024-01-01' AS DATE) AS [date], 1 AS expected_week
    UNION ALL SELECT '2025' AS [year], CAST('2025-01-01' AS DATE) AS [date], 53 AS expected_week
),
YearFirstWeek AS (
    SELECT 
        yd.[year],
        yd.[date],
        yd.expected_week,
        DATEFROMPARTS(CAST(yd.[year] AS INT), 1, 1) AS jan1,
        -- 计算当年第一个完整周的周一
        CASE DATEPART(weekday, DATEFROMPARTS(CAST(yd.[year] AS INT), 1, 1))
            WHEN 1 THEN DATEFROMPARTS(CAST(yd.[year] AS INT), 1, 1)
            ELSE DATEADD(day, 8 - DATEPART(weekday, DATEFROMPARTS(CAST(yd.[year] AS INT), 1, 1)), DATEFROMPARTS(CAST(yd.[year] AS INT), 1, 1))
        END AS first_week_monday
    FROM YearDates yd
),
CalculatedWeek AS (
    SELECT 
        yw.[year],
        yw.[date],
        yw.expected_week,
        DATENAME(WEEKDAY, yw.[date]) AS week_day,
        -- 计算自定义周数
        CASE 
            WHEN yw.[date] < yw.first_week_monday THEN 
                -- 计算上一年总周数
                (SELECT 
                    CASE 
                        WHEN DATEDIFF(week, 
                            CASE DATEPART(weekday, DATEFROMPARTS(CAST(yw.[year] AS INT)-1, 1, 1))
                                WHEN 1 THEN DATEFROMPARTS(CAST(yw.[year] AS INT)-1, 1, 1)
                                ELSE DATEADD(day, 8 - DATEPART(weekday, DATEFROMPARTS(CAST(yw.[year] AS INT)-1, 1, 1)), DATEFROMPARTS(CAST(yw.[year] AS INT)-1, 1, 1))
                            END,
                            DATEFROMPARTS(CAST(yw.[year] AS INT)-1, 12, 31)) + 1 = 53 THEN 53
                        ELSE 52
                    END
                )
            ELSE DATEDIFF(week, yw.first_week_monday, yw.[date]) + 1
        END AS custom_week
    FROM YearFirstWeek yw
)
SELECT 
    [year],
    DATEPART(ISO_WEEK, [date]) AS iso_week,
    custom_week AS calculated_week,
    expected_week,
    week_day
FROM CalculatedWeek
ORDER BY [year];

封装为可复用函数 #

如果需要频繁调用，可以创建标量函数：

SET DATEFIRST 1;

CREATE FUNCTION dbo.GetCustomWeekNumber(@date DATE)
RETURNS INT
AS
BEGIN
    DECLARE @year INT = YEAR(@date);
    DECLARE @jan1 DATE = DATEFROMPARTS(@year, 1, 1);
    -- 当年第一个完整周的周一
    DECLARE @firstWeekMonday DATE = 
        CASE DATEPART(weekday, @jan1)
            WHEN 1 THEN @jan1
            ELSE DATEADD(day, 8 - DATEPART(weekday, @jan1), @jan1)
        END;

    IF @date < @firstWeekMonday
    BEGIN
        -- 计算上一年总周数
        DECLARE @prevYear INT = @year - 1;
        DECLARE @prevJan1 DATE = DATEFROMPARTS(@prevYear, 1, 1);
        DECLARE @prevFirstWeekMonday DATE = 
            CASE DATEPART(weekday, @prevJan1)
                WHEN 1 THEN @prevJan1
                ELSE DATEADD(day, 8 - DATEPART(weekday, @prevJan1), @prevJan1)
            END;
        DECLARE @prevDec31 DATE = DATEFROMPARTS(@prevYear, 12, 31);
        RETURN CASE WHEN DATEDIFF(week, @prevFirstWeekMonday, @prevDec31) + 1 = 53 THEN 53 ELSE 52 END;
    END
    ELSE
    BEGIN
        RETURN DATEDIFF(week, @firstWeekMonday, @date) + 1;
    END
END;

调用示例：

SET DATEFIRST 1;

SELECT 
    '2020' AS [year],
    dbo.GetCustomWeekNumber('2020-01-01') AS custom_week,
    DATENAME(WEEKDAY, '2020-01-01') AS week_day;

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