Labor Day Vs. Thanksgiving

Question

I am creating a calendar table for my warehouse. I will use this as a foreign key for all the date fields.

The code shown below creates the table and populates it. I was able to figure out how to find Memorial Day (last Monday of May) and Labor Day (first Monday of September).

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

I am stumped on figuring out Thanksgiving day (Thursday of the last FULL week of November).

Answer 1

I am stumped on figuring out Thanksgiving day (Thursday of the last FULL week of November).

Last Saturday of November - 2

Answer 2

Take the last Saturday of November, and subtract two days ;)

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

For the complex algorithms, it's sometimes better to find a matching date from a set than trying to construct an enormous single-value formula.

See this article in my blog for more detail:

• Checking event dates