This function returns true if the date passed to it is a US holiday, else false. It needs two supporting functions shown below, easter_date() and lastmaymonday(), and uses the function described in "Common Queries" under "Date of Nth weekday of a month". Adjust as desired to make a similar function for another country's holidays.
drop function if exists is_us_holiday;
delimiter go
create function is_us_holiday( d date ) returns bool deterministic
begin
set @yr=year(d);
set @xmas=concat(@yr,'-12-25'), @newyr=concat(@yr,'-01-01');
return
case
-- SAT/SUN
when weekday(d) in(5,6) then true
-- NEW YEARS OR JAN 2 IF SUN
when d=@newyr then true
when weekday(@newyr)=6 && d=concat(@yr,'-01-02') then true
-- MARTIN LUTHER KING DAY
when d=dateofnthwkday( @yr, 1, 3, 0 ) then true
-- PRESIDENTS DAY
when d=dateofnthwkday( @yr, 2, 3, 0 ) then true
-- GOOD FRIDAY
when d=date_add(easter_date(@yr), interval -2 day) then true
-- MEMORIAL DAY
when d=lastmaymonday(@yr) then true
-- JULY 4
when d=date( concat(@yr,'-07-04' ) ) then true
-- LABOUR DAY
when d=dateofnthwkday( @yr, 9, 1, 0 ) then true
-- THANKSGIVING
when d=dateofnthwkday( @yr, 11, 4, 4 ) then true
-- XMAS OR ADJUSTMENT IF SUN
when d=concat(@yr,'-12-25') then true
when weekday(@xmas)=6 and d=date_add(@xmas, interval 1 day) then true
when weekday(@xmas)=5 and d=date_sub(@xmas, interval 1 day) then true
else false
end;
end;
go
delimiter ;
select is_us_holiday('2024-12-24');
-- SUPPORTING FUNCTIONS
drop function if exists easter_date;
delimiter go
create function easter_date(yr year) returns date deterministic
begin
declare a, b, c, d, e, k, m, n, p, q int;
declare easter date;
set k = floor( yr / 100 );
set a = mod( yr, 19 );
set b = mod( yr, 4 );
set c = mod( yr, 7 );
set q = floor( k / 4 );
set p = floor(( 13 + 8 * k) / 25 );
set m = mod( (15-p+k-q), 30 );
set d = mod( (19 * a + m), 30 );
set n = mod( (4+k-q), 7 );
set e = mod( (2*b+4*c+6*d+n), 7 );
return case
when d + e <= 9 then concat_ws('-', yr, '03', 22 + d + e)
when d = 29 and e = 6 then concat_ws('-', yr, '04-19')
when d = 28 and e = 6 and a > 10 then concat_ws('-', yr, '04-18')
else concat_ws('-', yr, '04', lpad(d + e - 9, 2, 0))
end;
end;
go
delimiter ;
select easter_date(2018);
delimiter ;
drop function if exists lastmaymonday;
delimiter go
create function lastmaymonday( yr year ) returns date deterministic
begin
declare ret date;
set ret = dateofnthwkday(yr,5,4,0);
return if( day(ret)>24, ret, date_add(ret, interval 7 day) );
end;
go
delimiter ;
select lastmaymonday(2018);
|
|