SQL Server 获取指定日期或时间段所属星期的周一和周日

获取单个日期所属星期的周一和周日

首先解决基础问题：给定一个具体的日期值，计算该日期所在的星期一和星期日。SQL Server 默认以星期日作为一周的第一天（即 DATEFIRST 为 7）。我们可以利用 DATEDIFF 计算当前日期距离基准日期（1900-01-01，星期一）的周数差，再通过 DATEADD 向前偏移得到当周的星期一；同理向后偏移 6 天即可得到当周的星期日。

第1步：创建单日期周范围函数

目的：封装逻辑，方便直接调用返回结果表。
参数：@tmpDate 输入日期。
返回值：包含 FirstDay（周一）和 EndDay（周日）的结果集。

USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[My_OneDay_GetWeekFirstAndEndDay](@tmpDate DATETIME)
RETURNS @tmpTable TABLE(FirstDay DATETIME , EndDay DATETIME)
AS
BEGIN
    INSERT INTO @tmpTable 
    SELECT a.FirstDay, b.EndDay FROM (
        -- 计算本周星期一的日期
        SELECT 1 AS ID, DATEADD(wk, DATEDIFF(wk, 0, @tmpDate), 0) AS FirstDAy
    ) a
    LEFT JOIN (
        -- 计算本周星期日的日期 (周一 + 6天)
        SELECT 1 AS ID, DATEADD(wk, DATEDIFF(wk, 0, @tmpDate), 6) AS EndDay
    ) b ON a.ID = b.ID
    RETURN
END;
GO

测试用例：

-- 测试日期：2010-09-01 (星期四)
-- 预期结果：星期一 2010-08-30，星期日 2010-09-05
SELECT * FROM My_OneDay_GetWeekFirstAndEndDay('2010-09-01');

获取时间段内所有星期的周一和周日

在实际业务中，如月度销售报表或考勤统计，通常需要列出从开始日期到结束日期之间，所经过的所有完整或不完整周次的起止时间。这可以通过在上述单日期函数的基础上，使用 WHILE 循环遍历每一天来实现。

第2步：创建时间段周范围函数

目的：迭代生成时间段内的每周周期。
参数：@tmpDateSTART 开始日期，@tmpDateEND 结束日期。
返回值：包含 WeekOrder（第几周）、FirstDay（周一）和 EndDay（周日）的排序结果表。

USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[MY_Range_GetWeekFirstAndEndDays](@tmpDateSTART DATETIME, @tmpDateEND DATETIME)
RETURNS @tmpTable TABLE(WeekOrder INT, FirstDay DATETIME , EndDay DATETIME)
AS
BEGIN
    DECLARE @tmpDate DATETIME
    DECLARE @index INT
    
    SET @tmpDate = @tmpDateSTART
    SET @index = 1
    
    -- 循环直到当前日期超过结束日期
    WHILE @tmpDate <= @tmpDateEND
    BEGIN
        INSERT INTO @tmpTable 
        SELECT @index, a.FirstDay, b.EndDay FROM (
            SELECT 1 AS ID, DATEADD(wk, DATEDIFF(wk, 0, @tmpDate), 0) AS FirstDAy
        ) a
        LEFT JOIN (
            SELECT 1 AS ID, DATEADD(wk, DATEDIFF(wk, 0, @tmpDate), 6) AS EndDay
        ) b ON a.ID = b.ID

        -- 每次循环增加7天
        SET @tmpDate = DATEADD(DAY, 7, @tmpDate)
        SET @index = @index + 1
    END
    RETURN
END;
GO

测试用例：

-- 测试区间：2011-09-01 至 2011-10-06
SELECT * FROM MY_Range_GetWeekFirstAndEndDays('2011-09-01', '2011-10-06');

执行上述测试，将得到一个包含 6 行记录的表格，依次列出从第一个星期一（2011-08-29）到最后一个星期天（2011-10-09）的各个周期。此方法能够自动处理跨月、跨年等边界情况，生成的日期表可直接用于连接主业务表进行聚合统计。

注意事项与优化建议

• 时区与格式：确保数据库会话的日期格式设置符合预期，建议始终使用 ISO 8601 标准格式（YYYY-MM-DD）传入日期参数以避免歧义。
• 性能考量：对于超长时间跨度（如几十年）的查询，循环逐日插入可能效率较低。若仅需统计每周维度，可考虑直接在查询中使用窗口函数或递归 CTE（Common Table Expression）进行生成，但在中小数据量下，本方案提供的存储函数简洁且易于理解。
• 自定义首周：如果业务要求“星期二”作为一周的开始，需要调整 DATEADD 中的偏移量或先设置 SET DATEFIRST 2，但修改系统级设置会影响全局行为，建议在函数内部通过数学计算硬编码偏移。

以上就是 SQL Server 判断指定日期或时间段所在星期的详细内容，更多关于数据库日期处理的技术资料请关注本站其它相关文章！