我有一个类似"2020-09-17"的日期，我想要这个："第三个星期二-2020-09-17'"，"第三"表示17/09是9月以来的第三个周二。
有什么想法可以通过使用SQL Server来解决这个问题吗？
我刚才把这个函数拼凑在一起它只在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
我不得不在下面更改此代码的实现，因为以前的解决方案无法处理某些日期，例如2020-07-13，当时thar按预期返回1而不是2。当然，我需要一些"；tunning hands"；在其中，但有一段时间我正在使用它来填充日历表。
create function dbo.getdayofweekinmonth( @date date )
returns int
as
begin
declare @dayofweek int = (datepart(dw, @date) + @@datefirst + 6 - 1) % 7 + 1;
declare @startofmonth date = datefromparts( year( @date ), month( @date ), 1 );
declare @month_days as table (d int) -- d as day number
while @startofmonth <= @date
begin
insert into @month_days values ((datepart(dw, @startofmonth) + @@datefirst + 6 - 1) % 7 + 1);
select @startofmonth = dateadd(day, 1, @startofmonth);
end
return (select count(*) from @month_days where d = @dayofweek)
end
为了获得更高的性能，最好使用日期维度表并将其与您自己的表连接(日期维度是一个每天有一条记录的表，包含您需要的关于某个日期的所有信息(