Here are a few handy calendar functions for US Holidays:
Create FUNCTION dateEasterSunday (p_year int)
RETURNS date DETERMINISTIC
BEGIN
declare p_day int DEFAULT 0;
declare p_month int DEFAULT 3;
declare p_g int DEFAULT 0;
declare p_c int DEFAULT 0;
declare p_h int DEFAULT 0;
declare p_i int DEFAULT 0;
SET p_g = p_year % 19;
SET p_c = p_year / 100;
SET p_h = (p_c - cast(p_c / 4 as int) - cast((8 * p_c + 13) / 25 as int) + 19 * p_g + 15) % 30;
SET p_i = p_h - cast(p_h / 28 as int) * (1 - cast(p_h / 28 as int) * cast(29 / (p_h + 1) as int) * cast((21 - p_g) / 11 as int));
SET p_day = p_i - ((p_year + cast(p_year / 4 as int) + p_i + 2 - p_c + cast(p_c / 4 as int)) % 7) + 28;
IF (p_day > 31) THEN
SET p_month = 4;
SET p_day = p_day - 31;
END IF;
RETURN str_to_date(concat(p_year, '-', p_month, '-', p_day), '%Y-%m-%d');
END;
drop function dateLaborDay;
Create FUNCTION dateLaborDay (p_year int)
RETURNS date DETERMINISTIC
BEGIN
declare v_days int default 0;
SET v_days = DAYOFWEEK(str_to_date(concat(p_year, ',09,01'), '%Y,%m,%d'));
/* 2 is Monday */
SET v_days = 2-v_days;
IF v_days < 0 THEN
RETURN date_add(str_to_date(concat(p_year, ',09,01'), '%Y,%m,%d'), interval 7 + v_days day);
ELSE
RETURN date_add(str_to_date(concat(p_year, ',09,01'), '%Y,%m,%d'), interval v_days day);
END IF;
END;
create FUNCTION dateMemorialDay (p_year int)
RETURNS date deterministic
BEGIN
declare v_day int default 31;
while DAYOFWEEK(str_to_date(concat(p_year, ',05,', v_day), '%Y,%m,%d')) <> 2 DO
SET v_day = v_day - 1;
end while;
RETURN str_to_date(concat(p_year, ',05,', v_day), '%Y,%m,%d');
END;
Create FUNCTION dateThanksgivingUSA (p_year int)
RETURNS date deterministic
BEGIN
declare v_day int default 1;
/* get first thursday */
while DAYOFWEEK(str_to_date(concat(p_year, ',11,', v_day), '%Y,%m,%d')) <> 5 DO
SET v_day = v_day + 1;
end while;
set v_day = v_day + 21;
RETURN str_to_date(concat(p_year, ',11,', v_day), '%Y,%m,%d');
END;
select dateLaborDay(year(now())), dateThanksgivingUSA(year(now())), dateMemorialDay(year(now())), dateEasterSunday(year(now()));
