Create bank holidays table
There are ten holidays that are observed by the Federal Reserve. Although some bank branches may be open, the Fed will be closed. When they are closed, ACH transactions like direct deposit and automatic debits do not occur. This impacts things like payroll or accounts receivable.
Some holidays always occur on a business day, like Memorial Day. What happens if a holiday like July 4 falls on a weekend? If a holiday falls on a Saturday, the Federal Reserve will be open for business as usual on the Friday before. But if a holiday falls on Sunday, they will be closed on the following Monday.
This script will create a new table, and flag dates that are holidays which are observed by the Federal Reserve. When the normal holiday is a Sunday, the script will also mark the following Monday as the observed holiday.
/*************************************************************************
created on: December 14, 2011
created by: Scott Thomas, AAP
This script will add a new table in your database that lists all Federal
Reserve holidays in the United States. These are the holidays when
banks are closed, and no ACH transactions occur. (No direct deposits or
automatic debits from checking accounts).
If a holiday falls on a Sunday, The Federal Reserve Bank will be closed on
the following Monday, and no transactions will occur. But if a holiday
falls on Saturday, the Federal Reserve Bank will be open on the Friday
before the holiday (business as usual, transactions will be processed).
**************************************************************************/
-- Declare the first and last dates that will be inserted to the new table.
-- You can alter these dates as needed. These are the only variables you
-- may need to change.
declare @FirstDate as date
declare @LastDate as date
declare @WorkingDate as date
set @FirstDate = '2000-01-01'
set @LastDate = '2099-12-31'
-- create the new table
begin
CREATE TABLE dbo.FederalReserveHolidays
(
[Date] date NOT NULL,
BankHoliday nvarchar(1) NULL,
HolidayName nvarchar(50) NULL
) ON [PRIMARY]
end
-- add primary key
begin
ALTER TABLE dbo.FederalReserveHolidays ADD CONSTRAINT
PK_FederalReserveHolidays PRIMARY KEY CLUSTERED
(
Date
) WITH( STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
end
-- insert the first date
INSERT INTO dbo.FederalReserveHolidays
([Date],[BankHoliday])
VALUES
(@FirstDate,'N')
-- insert the remaining dates by adding 1 to the last date
While (select MAX(Date)
from dbo.FederalReserveHolidays
) < @LastDate
begin
set @WorkingDate = DATEADD(day,1,(select MAX(Date) from dbo.FederalReserveHolidays ))
if @WorkingDate <= @LastDate
begin
INSERT INTO dbo.FederalReserveHolidays
([Date],[BankHoliday])
VALUES
(@WorkingDate, 'N')
end
else
break
end
-- identify the Federal holidays
begin
-- New Year's Day, January 1
-- If January 1 is a Sunday, the holdiay is observed on Monday
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'New Year''s Day'
where DATEPART(day,Date) = 1
and DATEPART(month,Date) = 1
and DATEPART(Dw,Date) between 2 and 6
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'New Year''s Day'
where DATEPART(day,Date) = 2
and DATEPART(month,Date) = 1
and DATEPART(Dw,Date) = 2
end
begin
-- Martin Luther King's birthday, third Monday in January
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Martin Luther King Day'
where DATEPART(day,Date) between 15 and 21
and DATEPART(month,Date) = 1
and DATEPART(Dw,Date) = 2
end
begin
-- President's Day, third Monday in February
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'President''s Day'
where DATEPART(day,Date) between 15 and 21
and DATEPART(month,Date) = 2
and DATEPART(Dw,Date) = 2
end
begin
-- Memorial Day, last Monday in May
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Memorial Day'
where DATEPART(day,Date) between 25 and 31
and DATEPART(month,Date) = 5
and DATEPART(Dw,Date) = 2
end
begin
-- Independence Day, July 4
-- If July 4 is a Sunday, the holdiay is observed on Monday
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Independence Day'
where DATEPART(day,Date) = 4
and DATEPART(month,Date) = 7
and DATEPART(Dw,Date) between 2 and 6
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Independence Day'
where DATEPART(day,Date) = 5
and DATEPART(month,Date) = 7
and DATEPART(Dw,Date) = 2
end
begin
-- Labor Day, first Monday in September
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Labor Day'
where DATEPART(day,Date) between 1 and 7
and DATEPART(month,Date) = 9
and DATEPART(Dw,Date) = 2
end
begin
-- Columbus Day, second Monday in October
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Columbus Day'
where DATEPART(day,Date) between 8 and 14
and DATEPART(month,Date) = 10
and DATEPART(Dw,Date) = 2
end
begin
-- Veteran's Day, November 11
-- If November 11 is a Sunday, the holdiay is observed on Monday
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Veteran''s Day'
where DATEPART(day,Date) = 11
and DATEPART(month,Date) = 11
and DATEPART(Dw,Date) between 2 and 6
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Veteran''s Day'
where DATEPART(day,Date) = 12
and DATEPART(month,Date) = 11
and DATEPART(Dw,Date) = 2
end
begin
-- Thanksgiving, fourth Thursday in November
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Thanksgiving'
where DATEPART(day,Date) between 22 and 28
and DATEPART(month,Date) = 11
and DATEPART(Dw,Date) = 5
end
begin
-- Christmas, December 25
-- If December 25 is a Sunday, the holdiay is observed on Monday
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Christmas'
where DATEPART(day,Date) = 25
and DATEPART(month,Date) = 12
and DATEPART(Dw,Date) between 2 and 6
update dbo.FederalReserveHolidays
set BankHoliday = 'Y',
HolidayName = 'Christmas'
where DATEPART(day,Date) = 26
and DATEPART(month,Date) = 12
and DATEPART(Dw,Date) = 2
end
