Calculate Thanksgiving, Easter Sunday, Memorial Day, and Labor Day out to 2050 by only passing in year as integer.
2007-10-02 (first published: 2002-06-20)
15,446 reads
Calculate Thanksgiving, Easter Sunday, Memorial Day, and Labor Day out to 2050 by only passing in year as integer.
CREATE FUNCTION GetHolidays( @Year INT = 0 ) RETURNS @Holidays TABLE( ItemID INT, Holiday_Title VARCHAR(50), Holiday_Date DATETIME ) AS BEGIN DECLARE @Month INT DECLARE @Day INT DECLARE @Holiday VARCHAR(50) DECLARE @HolidayDate DATETIME DECLARE @TmpDate VARCHAR(10) DECLARE @ItemCount INT DECLARE @Now DATETIME -- Calculate Easter Sunday -- Alogrithm modeled after VB2TheMax code DECLARE @g INT DECLARE @c INT DECLARE @h INT DECLARE @i INT DECLARE @j INT DECLARE @l INT SET @g = @Year % 19 SET @c = @Year / 100 SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30) SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11))) SET @j = ((@Year + (@Year / 4) + @i + 2 - @c + (@c / 4)) % 7) SET @l = @i - @j SET @Month = 3 + ((@l + 40) / 44) SET @Day = @l + 28 - (31 * (@Month / 4)) SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) SET @Holiday = 'Easter Sunday' SET @HolidayDate = CAST(@TmpDate AS DATETIME) SET @ItemCount = 1 INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate) -- Calculate Thanksgiving SET @Holiday = 'Thanksgiving' SET @Month = 11 SET @ItemCount = @ItemCount + 1 DECLARE @X INT DECLARE @TCount INT SET @TCount = 0 SET @X = 1 WHILE @X <= 30 BEGIN IF DATENAME(DW, CAST(CAST(@Month AS VARCHAR(2)) + '/' + CAST(@X AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Thursday' BEGIN SET @TCount = @TCount + 1 IF @TCount = 4 BEGIN BREAK END END SET @X = @X + 1 END SET @Day = @X SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) SET @HolidayDate = CAST(@TmpDate AS DATETIME) INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate) -- Calculate Memorial Day SET @ItemCount = @ItemCount + 1 SET @Month = 5 SET @Holiday = 'Memorial Day' SET @X = 1 WHILE @X <= 31 BEGIN IF DATENAME(DW, CAST('5/' + CAST(@X AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Monday' BEGIN SET @Day = @X END IF @X = 31 BEGIN BREAK END SET @X = @X + 1 END SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) SET @HolidayDate = CAST(@TmpDate AS DATETIME) INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate) -- Calculate Labor Day SET @ItemCount = @ItemCount + 1 SET @Month = 9 SET @Holiday = 'Labor Day' SET @X = 1 WHILE @X <= 8 BEGIN IF DATENAME(DW, CAST('9/' + CAST(@X AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Monday' BEGIN SET @Day = @X BREAK END SET @X = @X + 1 END SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) SET @HolidayDate = CAST(@TmpDate AS DATETIME) INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate) RETURN END