劳动节比。感恩(Labor Day Vs. Thanksgiving)

我为我的仓库创建日历表。我将以此作为所有日期字段的外键。

如下所示的代码创建表和填充。我能弄清楚如何找到阵亡将士纪念日（五月的最后一个星期一），劳动节（九月的第一个星期一）。

SET NOCOUNT ON

DROP Table dbo.Calendar
GO
Create Table dbo.Calendar
(
    CalendarId              Integer NOT NULL,
    DateValue               Date    NOT NULL,
    DayNumberOfWeek         Integer NOT NULL,
    NameOfDay               VarChar (10) NOT NULL,
    NameOfMonth             VarChar (10) NOT NULL,
    WeekOfYear              Integer NOT NULL,
    JulianDay               Integer NOT NULL,
    USAIsBankHoliday        Bit     NOT NULL,
    USADayName              VarChar (100) NULL,
)
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USADayName DEFAULT '' FOR USADayName
GO

Declare @StartDate  DateTime = '01/01/2000'
Declare @EndDate    DateTime = '01/01/2020'

While @StartDate < @EndDate
Begin
    INSERT INTO dbo.Calendar 
    (
        CalendarId, 
        DateValue, 
        WeekOfYear,
        DayNumberOfWeek,
        NameOfDay,
        NameOfMonth,
        JulianDay
    )
    Values 
    (
        YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
        @StartDate,                 -- DateValue
        DATEPART (ww, @StartDate),  -- WeekOfYear
        DATEPART (dw, @StartDate),  -- DayNumberOfWeek
        DATENAME (dw, @StartDate),  -- NameOfDay
        DATENAME (M, @StartDate),   -- NameOfMonth
        DATEPART (dy, @StartDate)   -- JulianDay
    )

    Set @StartDate += 1
End

--=========================== Weekends
-- saturday and sunday
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7) 


--=========================== Bank Holidays
-- new years day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, '  WHERE (CalendarId % 2000) IN (101)

-- memorial day (last Monday in May)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Memorial Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MAX (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 5 
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- independence day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, '  WHERE (CalendarId % 2000) IN (704)

-- labor day (first Monday in September)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Labor Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MIN (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 9
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- thanksgiving day (fourth Thursday in November)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Thanksgiving Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT Max (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 11
        AND DATEPART (DW, DateValue)=5
        GROUP BY YEAR (datevalue)
    )

-- christmas
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, '  WHERE (CalendarId % 2000) IN (1225)

--=========================== Other named days
-- new years eve
UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, '  WHERE (CalendarId % 2000) IN (1231)

-- christmas eve
UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, '  WHERE (CalendarId % 2000) IN (1224)

-- boxing day
UPDATE dbo.Calendar SET USADayName += 'Boxing Day, '  WHERE (CalendarId % 2000) IN (1226)

--=========================== Remove trailing comma
UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) > 2

SELECT * FROM dbo.Calendar

我难倒搞清楚感恩节（11月最后一个整周的星期四）。

编辑：修正基于由约翰·索尔评论

感恩节是十一月的第四个星期四。然而，在检查的几年里，我发现它已经被证明也是在11月的最后一个整周的周四

Answer 1:

我难倒搞清楚感恩节（11月最后一个整周的星期四）。

十一月的最后一个星期六 - 2

Answer 2:

就拿月的最后一个星期六，然后减去2天）

Answer 3:

WITH    cal AS
        (
        SELECT  CAST('2009-30-11' AS DATETIME) AS cdate
        UNION ALL
        SELECT  DATEADD(day, -1, cdate)
        FROM    cal
        WHERE   cdate >= '2009-01-11'
        )
SELECT  TOP 1 DATEADD(day, -2, cdate)
FROM    cal
WHERE   DATEPART(weekday, cdate) = 6

对于复杂的算法，它有时更好地找到一组匹配的日期不是试图建造一个巨大的单值的公式。

看到这篇文章在我的博客的更多详细信息：

检查活动日期

Answer 4:

declare @thedate datetime
set @thedate = '11/24/2011'

select 1 
where datepart(dw, @thedate) = 5 and datepart(m, @thedate) = 11 AND (datepart(dd, @thedate) - 21) between 0 and 6

在十一月的日期星期四，是有小于剩余一个星期。