sql-server - 劳动节与感恩

我正在为我的仓库创建一个日历表。我将其用作所有日期字段的外键。

下面显示的代码创建表格并填充该表格。我能够弄清楚如何找到阵亡将士纪念日(5月的最后一个星期一)和劳动节(9月的第一个星期一)。

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月的最后一个完整周的星期四)而感到困惑。

编辑:
根据的评论进行更正约翰· Solr

感恩节是十一月的第四个星期四。但是，经过数年的考察，我发现它还是11月最后一个完整周的星期四。

最佳答案

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

关于sql-server - 劳动节与感恩，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1201643/