SELECT CASE WHEN (MONTH(GETDATE()) = 7 AND DAY(GETDATE()) = 4 ) OR -- U.S. Independence Day
(MONTH(GETDATE()) = 1 AND DAY(GETDATE()) = 1 ) OR -- New Year's Day
(MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 25) OR -- Christmas Day
(MONTH(GETDATE()) = 11 AND DAY(GETDATE()) BETWEEN 22 AND 28
AND DATENAME(DW, GETDATE()) = 'Thursday') OR -- Thanksgiving
(MONTH(GETDATE()) = 5 AND DAY(GETDATE()) BETWEEN 25 AND 31
AND DATENAME(DW, GETDATE()) = 'Monday') OR -- Memorial Day
(MONTH(GETDATE()) = 9 AND DAY(GETDATE()) BETWEEN 1 AND 7
AND DATENAME(DW, GETDATE()) = 'Monday') -- Labor Day
THEN 'Holiday'
ELSE 'Not a Holiday' END AS [Is US Holiday]