Oracle Holidays without Stored Procedure with 2 Week Notification

A client had a need for notifying others of upcoming holidays in some automated reports. Instead of manually adding these holidays to the reports as they were approaching, it was suggested that the dates be added to the report automatically. Through the use of SQL, the below code was created. It is not flawless, but does provide advance notification that met the client's needs.

MLK Day, 3rd Monday in January

select 'Upcoming Holiday and Special Processing: Martin Luther King Day is on ' || 
(select to_date(NEXT_DAY(to_date((select '14-JAN-' || to_char(sysdate, 'YY') from dual)),'Monday'))  from dual where 
sysdate >= (select to_date(NEXT_DAY(to_date((select '14-JAN-' || to_char(sysdate, 'YY') from dual)),'Monday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '14-JAN-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual);

President's Day, 3rd Monday in February

select 'Upcoming Holiday and Special Processing: President''s Day is on ' || 
(select to_date(NEXT_DAY(to_date((select '14-FEB-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual)  from dual where 
sysdate >= (select to_date(NEXT_DAY(to_date((select '14-FEB-' || to_char(sysdate, 'YY') from dual)),'Monday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '14-FEB-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual);

Daylight Savings Time Start, 2nd Sunday in March

select 'Upcoming Special Processing: Daylight Savings Time Starts on ' ||
(select to_date(NEXT_DAY(to_date((select '1-MAR-' || to_char(sysdate, 'YY') from dual)),'Sunday')) from dual)  from dual where
sysdate >= (select to_date(NEXT_DAY(to_date((select '1-MAR-' || to_char(sysdate, 'YY') from dual)),'Monday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '1-MAR-' || to_char(sysdate, 'YY') from dual)),'Sunday')) from dual);

Memorial Day, last Monday in May

select 'Upcoming Holiday and  Special Processing: Memorial Day is on ' || 
(select to_date(NEXT_DAY(to_date((select '24-MAY-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual)  from dual where 
sysdate >= (select to_date(NEXT_DAY(to_date((select '24-MAY-' || to_char(sysdate, 'YY') from dual)),'Monday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '24-MAY-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual);

Independence Day, July 4th

select 'Upcoming Holiday and  Special Processing: Independence Day is on 04-JUL-' || 
(select to_char(sysdate, 'YY') from dual)  from dual
where (to_char(sysdate, 'Mon') = 'Jun' and sysdate >= to_date('20', 'DD') AND
(to_char(sysdate, 'Mon') = 'Jul' and sysdate <= to_date('05', 'DD') );

Labor day, 1st Monday in September

select 'Upcoming Holiday and Special Processing: Labor Day is on ' ||
(select to_date(NEXT_DAY(to_date((select '31-AUG-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual)  from dual where 
sysdate >= (select to_date(NEXT_DAY(to_date((select '31-AUG-' || to_char(sysdate, 'YY') from dual)),'Monday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '31-AUG-' || to_char(sysdate, 'YY') from dual)),'Monday')) from dual);

Daylight Savings Time Ends, 1st Sunday in November

select 'Upcoming Special Processing: Daylight Savings Time Ends on ' || 
(select to_date(NEXT_DAY(to_date((select '31-OCT-' || to_char(sysdate, 'YY') from dual)),'Sunday')) from dual)  from dual where
sysdate >= (select to_date(NEXT_DAY(to_date((select '31-OCT-' || to_char(sysdate, 'YY') from dual)),'Sunday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '31-OCT-' || to_char(sysdate, 'YY') from dual)),'Sunday')) from dual);

Veterans Day, November 11th

select 'Upcoming Holiday and Special Processing: Veteran''s Day is on 11-NOV-' || 
(select to_char(sysdate, 'YY') from dual)  from dual
where (to_char(sysdate, 'Mon') = 'Oct' and sysdate >= to_date('28', 'DD')) AND 
(to_char(sysdate, 'Mon') = 'Nov' and sysdate <= to_date('11', 'DD'));

Thanksgiving and Day after Thanksgiving (Black Friday), 4th Thursday in November

select 'Upcoming Holiday and Special Processing: Thanksgiving and Day After Thanksgiving are on ' || 
(select to_date(NEXT_DAY(to_date((select '21-NOV-' || to_char(sysdate, 'YY') from dual)),'Thursday')) from dual) || ' and ' ||
(select to_date(NEXT_DAY(to_date((select '21-NOV-' || to_char(sysdate, 'YY') from dual)),'Friday')) from dual)  from dual where 
sysdate >= (select to_date(NEXT_DAY(to_date((select '21-NOV-' || to_char(sysdate, 'YY') from dual)),'Thursday'))-14 from dual) and
sysdate <= (select to_date(NEXT_DAY(to_date((select '21-NOV-' || to_char(sysdate, 'YY') from dual)),'Friday')) from dual);

Christmas, December 25th

select 'Upcoming Holiday and Special Processing: Christmas Day is on 25-DEC-' || 
(select to_char(sysdate, 'YY') from dual)  from dual  
where to_char(SYSDATE, 'Mon') = 'Dec' AND 
to_char(sysdate, 'W') in ('2', '3', '4') and sysdate <= to_date('26', 'DD');

New Year's Eve and New Year's Day, January 1

select 'Upcoming Holiday and Special Processing: New Year''s Day is on 1-JAN-' || 
(select to_char(sysdate+365, 'YY') from dual)  from dual  
where (to_char(SYSDATE, 'Mon') = 'Dec' AND to_char(sysdate, 'W') in ('2', '3', '4', '5'));

select 'Upcoming Holiday and Special Processing: New Year''s Day is on 1-JAN-' || 
(select to_char(sysdate, 'YY') from dual)  from dual where
(to_char(sysdate, 'Mon') = 'Jan' and sysdate <= to_date('02', 'DD'));