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

Rate

3.3 (10)

Share

Share

Rate

3.3 (10)