sql - 获取“一天中的第几周”？

我刚才拼凑了这个功能。它只在SET DATEFIRST 1. 修改此函数以使用星期一不是一周的第一天的日历是读者的练习（注意：这样做很重要）。

SET DATEFIRST 1; -- Set Monday as the first day-of-week.

CREATE FUNCTION dbo.GetDayOfWeekInMonth( @date date )
RETURNS int
AS
BEGIN

    --SET DATEFIRST 1; -- can't use this inside a function

    DECLARE @dayOfMonth            int  = DATEPART( day    , @date );
    DECLARE @dayOfWeek             int  = DATEPART( weekday, @date );

    DECLARE @startOfMonth          date = DATEFROMPARTS( YEAR( @date ), MONTH( @date ), 1 );
    DECLARE @startOfMonthDayOfWeek int  = DATEPART( weekday, @startOfMonth );

    DECLARE @weekNumber            int  = @dayOfMonth / 7;

    DECLARE @dayOfWeekInMonth      int  = CASE WHEN @startOfMonthDayOfWeek > @dayOfWeek THEN @weekNumber ELSE ( @weekNumber + 1 ) END;
    RETURN @dayOfWeekInMonth

END

例子：

所以运行这个：

SET DATEFIRST 1

DECLARE @t TABLE ( Dt date NOT NULL, DoW int NOT NULL, DoWName nvarchar(20) NOT NULL, DoWoM int NOT NULL );

DECLARE @d int = 1
WHILE @d <= 30
BEGIN
    
    DECLARE @dt date = DATEFROMPARTS( 2020, 9, @d );

    INSERT INTO @t ( Dt, DoW, DoWName, DoWoM )
    VALUES (
        @dt,
        DATEPART( weekday, @dt ),
        DATENAME( weekday, @dt ),
        dbo.GetDayOfWeekInMonth( @dt )
    );

    SET @d = @d + 1

END;

SELECT
    *,
    CASE DoWoM
        WHEN 1 THEN CONCAT( 'First ' , DoWName, ' of ', DATENAME( month, Dt ) )
        WHEN 2 THEN CONCAT( 'Second ', DoWName, ' of ', DATENAME( month, Dt ) )
        WHEN 3 THEN CONCAT( 'Third ' , DoWName, ' of ', DATENAME( month, Dt ) )
        WHEN 4 THEN CONCAT( 'Fourth ', DoWName, ' of ', DATENAME( month, Dt ) )
        WHEN 5 THEN CONCAT( 'Fifth ' , DoWName, ' of ', DATENAME( month, Dt ) ) ELSE NULL
    END AS [Text]
FROM
    @t;

给我这个输出：

Dt            DoW    DoWName    DoWoM    Text
2020-09-01    2      Tuesday    1        First Tuesday of September
2020-09-02    3      Wednesday  1        First Wednesday of September
2020-09-03    4      Thursday   1        First Thursday of September
2020-09-04    5      Friday     1        First Friday of September
2020-09-05    6      Saturday   1        First Saturday of September
2020-09-06    7      Sunday     1        First Sunday of September
2020-09-07    1      Monday     1        First Monday of September
2020-09-08    2      Tuesday    2        Second Tuesday of September
2020-09-09    3      Wednesday  2        Second Wednesday of September
2020-09-10    4      Thursday   2        Second Thursday of September
2020-09-11    5      Friday     2        Second Friday of September
2020-09-12    6      Saturday   2        Second Saturday of September
2020-09-13    7      Sunday     2        Second Sunday of September
2020-09-14    1      Monday     2        Second Monday of September
2020-09-15    2      Tuesday    3        Third Tuesday of September
2020-09-16    3      Wednesday  3        Third Wednesday of September
2020-09-17    4      Thursday   3        Third Thursday of September
2020-09-18    5      Friday     3        Third Friday of September
2020-09-19    6      Saturday   3        Third Saturday of September
2020-09-20    7      Sunday     3        Third Sunday of September
2020-09-21    1      Monday     3        Third Monday of September
2020-09-22    2      Tuesday    4        Fourth Tuesday of September
2020-09-23    3      Wednesday  4        Fourth Wednesday of September
2020-09-24    4      Thursday   4        Fourth Thursday of September
2020-09-25    5      Friday     4        Fourth Friday of September
2020-09-26    6      Saturday   4        Fourth Saturday of September
2020-09-27    7      Sunday     4        Fourth Sunday of September
2020-09-28    1      Monday     4        Fourth Monday of September
2020-09-29    2      Tuesday    5        Fifth Tuesday of September
2020-09-30    3      Wednesday  5        Fifth Wednesday of September

sql - 获取“一天中的第几周”？

问题描述

解决方案

例子：

推荐阅读