LISTING 1: Code That Generates Business Holiday Dates

SET NOCOUNT ON

-- Set DATEFIRST to US English default, 7, for Sunday.
-- Thus, when GetDate() is Sunday, DATEPART(dw,GETDATE()) = 1
SET DATEFIRST 7
SET LANGUAGE N'us_english'

-- Set up the year.
DECLARE @year TABLE(year_str VARCHAR(4))
DECLARE @day TABLE(day_int SMALLINT)
DECLARE @i SMALLINT

INSERT @year VALUES('2002')
INSERT @year VALUES('2003')
INSERT @year VALUES('2004')

-- Set up a table of day-of-month values.
SELECT @i = 1
WHILE @i < 32
	BEGIN
		INSERT @day VALUES(@i)
		SELECT @i = @i + 1
	END

-- Union all the holidays together.
SELECT
'NewYears' AS Holiday,year_str AS [Year],
CASE
WHEN DATEPART(dw,CONVERT(DATETIME,'01/01/'+year_str))= 1 THEN 
DATEADD(dd,1,CONVERT(DATETIME,'01/01/'+year_str))
WHEN DATEPART(dw,CONVERT(DATETIME,'01/01/'+year_str))= 7 THEN DATEADD(dd,-
1,CONVERT(DATETIME,'01/01/'+year_str))
ELSE CONVERT(DATETIME,'01/01/'+year_str)
END AS [Date]
FROM @year
UNION

-- Lee-Jackson-King Day: Third Monday in January:
-- (MAX) WHERE dw = 2 and day_int < 22
SELECT 'LeeJacksonKing', 
year_str,MAX(CONVERT(DATETIME,'01/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str))
FROM @day,@year
WHERE 
DATEPART(dw,CONVERT(DATETIME,'01/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str)) = 2
AND day_int < 22
GROUP BY year_str
UNION

-- Memorial Day: Last Monday in May: (MAX) WHERE dw = 2
SELECT 'MemorialDay', 
year_str,MAX(CONVERT(DATETIME,'05/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str))
FROM @day,@year
WHERE 
DATEPART(dw,CONVERT(DATETIME,'05/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str)) = 2
GROUP BY year_str
UNION

SELECT 'FourthOfJuly',year_str,
CASE
WHEN DATEPART(dw,CONVERT(DATETIME,'07/04/'+year_str))= 1 THEN 
CONVERT(DATETIME,'07/05/'+year_str)
WHEN DATEPART(dw,CONVERT(DATETIME,'07/04/'+year_str))= 7 THEN 
CONVERT(DATETIME,'07/03/'+year_str)
ELSE CONVERT(DATETIME,'07/04/'+year_str)
END
FROM @year
UNION

-- Labor Day: First Monday in September: MIN WHERE dw = 2
-- Keep day_int less than 31 because 9/31 is an out-of-range DATETIME value.
SELECT'LaborDay', 
year_str,MIN(CONVERT(DATETIME,'09/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str))
FROM @day,@year
WHERE 
DATEPART(dw,CONVERT(DATETIME,'09/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str)) = 2
AND day_int < 31
GROUP BY year_str
UNION

-- Thanksgiving: Fourth Thursday in November: MAX WHERE dw = 5 and day_int < 29
SELECT
'Thanksgiving', 
year_str,MAX(CONVERT(DATETIME,'11/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str))
FROM @day,@year
WHERE 
DATEPART(dw,CONVERT(DATETIME,'11/'+CONVERT(VARCHAR(2),day_int)+'/'+year_str)) = 5
AND day_int < 29
GROUP BY year_str

UNION
SELECT 'Christmas',year_str,
CASE
WHEN DATEPART(dw,CONVERT(DATETIME,'12/25/'+year_str))= 1 THEN 
CONVERT(DATETIME,'12/26/'+year_str)
WHEN DATEPART(dw,CONVERT(DATETIME,'12/25/'+year_str))= 7 THEN 
CONVERT(DATETIME,'12/24/'+year_str)
ELSE CONVERT(DATETIME,'12/25/'+year_str)
END
FROM @year

ORDER BY 3