一段时间段内的星期几到星期几的集合_一段日期内的各周的集合-优快云博客

本文链接：https://blog.youkuaiyun.com/issacp/article/details/4045792

本文介绍了一个使用T-SQL创建存储过程的例子，该过程用于查询指定日期范围内的工作日最大日期。通过创建临时表并利用`DATEADD`和`DATEPART`函数，此存储过程能够灵活地筛选出特定周几的最大日期。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROC
    p @start_dt DATETIME,
    @end_dt DATETIME,
    @weekdays VARCHAR(20)
AS
    DECLARE @sSQL NVARCHAR(4000);
    DECLARE @columns NVARCHAR(1000)
    SELECT @sSQL='',@columns='';

    CREATE TABLE #weekday_table(ID TINYINT IDENTITY,[weekday] INT);
    INSERT #weekday_table([weekday])
        SELECT
            SUBSTRING(@weekdays,number,
                        CHARINDEX(',',@weekdays+',',number)-number)
        FROM master.dbo.spt_values AS A
        WHERE A.type='p'
            AND number BETWEEN 1 AND LEN(@weekdays)
            AND SUBSTRING(','+@weekdays,number,1)=',';

    SELECT
        @columns=@columns+N',MAX(CASE WHEN [weekday]='+
                          RTRIM([weekday])+N' THEN dt END) AS [周'+
                            CASE [weekday]
                                WHEN 0 THEN N'日'
                                WHEN 1 THEN N'一'
                                WHEN 2 THEN N'二'
                                WHEN 3 THEN N'三'
                                WHEN 4 THEN N'四'
                                WHEN 5 THEN N'五'
                                WHEN 6 THEN N'六' END +N']'
    FROM #weekday_table;

    SET @columns=STUFF(@columns,1,1,'');

    SET @sSQL=N'
        SELECT '+@columns+N'
        FROM (
            SELECT
               DATEADD(day,number,@start_dt) AS dt,
               (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7 AS [weekday],
               DATEPART(week,DATEADD(day,number,@start_dt)) AS [week]
            FROM master.dbo.spt_values AS A
                JOIN #weekday_table AS B
                    ON (DATEPART(weekday,DATEADD(day,number,@start_dt))+@@DATEFIRST-1)%7=B.[weekday]
            WHERE A.type=''p''
            AND DATEADD(day,number,@start_dt)<=@end_dt
        ) AS A
        GROUP BY [week]
    ';

    EXEC sp_executesql @sSQL,
             N'@start_dt DATETIME,@end_dt DATETIME',
             @start_dt,@end_dt;

GO

EXEC p '2009-05-01','2009-05-31','0,1,2,3,4,5,6'

GO
DROP PROC p