Blog

 
     

UK Bank Holiday Function

Mar 14 2014

On my current contract I made the suggestion that our date dimension should contain bank holidays as they would enrich the reporting capability, I had previously written a function to return a list of hank holidays but I had only really considered modern holidays from 2000 onwards so I needed to make a few mods to take into account the changing face of bank holidays over the years. 

I decided to create this as a scalar rather than table valued function to really just aid use, it only is targeted to return holidays that occurred after 1900 so keep that in mind!

Also it should be noted that whilst this will help you populate bank/public holidays in your date dimension you really should consider creating a special events dimension to allow you to cater for foreign holidays and global events that would enrich your data. 

File download is at the bottom of this post.

CREATE FUNCTION [transform].[usfGetBankHoliday] (@CheckDate DATE)
RETURNS NVARCHAR(50)
AS
/* 
Author                  : Mike Spraggett (Damutu Ltd)
Usage                   : SELECT [transform].[usfGetBankHoliday] ('2014-01-01')
Description             : Returns a string containing the name of the bank holiday that fell on the day passed to the function 
*/
BEGIN
DECLARE  @Date date
 
 
/* 
--------------------------
New Years Day
--------------------------
Officially recognised as a bank holiday in 1974, previously observed and unofficial
If it occurs on a weekend then the bank holiday rolled forward to the immediately following Monday
*/
IF MONTH(@CheckDate) = 1 AND YEAR(@CheckDate) >= 1974
    BEGIN
        SET @Date=CONVERT(date,CONVERT(varchar,YEAR(@CheckDate))+'-01-01' ) 
        IF CONVERT(VARCHAR(9),CONVERT(VARCHAR(9),DATENAME(dw,@Date))) = 'Saturday'
            SET @Date=DATEADD(day,2,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,1,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'New Years'' Day'
            END
    END
 
/* 
--------------------------
Christmas Day
--------------------------
Has been considered a public holiday from before 1900
If it occurs on a weekend then the bank holiday rolled forward to the immediately following Monday
*/
IF MONTH(@CheckDate) = 12
    BEGIN
        SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-12-25' ) 
        IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
            SET @Date=DATEADD(day,2,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,1,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'Christmas Day'
            END
    END
     
/* 
--------------------------
Boxing Day
--------------------------
Officially recognised as a bank holiday in 1974, previously observed locally
If it occurs on a weekend then the bank holiday rolled forward to immediately follow Christmas day
*/
IF MONTH(@CheckDate) = 12 AND YEAR(@CheckDate) > 1974
    BEGIN
        SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-12-26' ) 
        IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
            SET @Date=DATEADD(day,3,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,2,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'Boxing Day'
            END
    END
     
/* 
--------------------------
May Day
--------------------------
Officially recognised as a bank holiday in 1978.
If it occurs on a weekend then the bank holiday rolled forward to the following Monday
In 1995 & 2005 it was moved to the 8th of May to allow for VE day
*/
IF MONTH(@CheckDate) = 5 AND YEAR(@CheckDate) > 1978
    BEGIN
        SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-05-01' ) 
        -- Exceptions
        IF YEAR(@Date) IN ('2005','1995')
            SET @Date=DATEADD(day,7,@Date)
        -- Standard
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Tuesday'
            SET @Date=DATEADD(day,6,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Wednesday'
            SET @Date=DATEADD(day,5,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Thursday'
            SET @Date=DATEADD(day,4,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Friday'
            SET @Date=DATEADD(day,3,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
            SET @Date=DATEADD(day,2,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,1,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'May Day'
            END
    END
 
     
/* 
--------------------------
Spring Bank Holiday
--------------------------
Officially recognised as a bank holiday in 1978.
Until 1965 the spring bank holiday was the Monday after Pentecost, subsequently it was the last monday in May
*/
IF YEAR(@CheckDate) > 1977 AND MONTH(@CheckDate) IN (5,6)
    BEGIN
    SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-05-25' ) 
    -- Exceptions
    IF YEAR(@Date) IN ('2012') -- Moved for Diamond Jubilee
        SET @Date=CONVERT(DATE,'2012-06-04')
    ELSE IF YEAR(@Date) IN ('2002') -- Moved for Golden Jubilee
        SET @Date=CONVERT(DATE,'2002-06-04')
    -- Standard
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Tuesday'
        SET @Date=DATEADD(day,6,@Date)
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Wednesday'
        SET @Date=DATEADD(day,5,@Date)
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Thursday'
        SET @Date=DATEADD(day,4,@Date)
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Friday'
        SET @Date=DATEADD(day,3,@Date)
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
        SET @Date=DATEADD(day,2,@Date)
    ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
        SET @Date=DATEADD(day,1,@Date)
    IF (@Date = @CheckDate)
        BEGIN
            RETURN 'Spring Bank Holiday'
        END
    END
ELSE IF YEAR(@CheckDate) < 1978 AND MONTH(@CheckDate) IN (5,6) 
    BEGIN
    SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-01-01' ) 
    DECLARE @EpactCalc INT,  
            @PaschalDaysCalc INT, 
            @NumOfDaysToSunday INT, 
            @EasterMonth INT, 
            @EasterDay INT
 
        SET @EpactCalc = (24 + 19 * (YEAR(@Date) % 19)) % 30 
        SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 
        SET @NumOfDaysToSunday = @PaschalDaysCalc - ( 
            (YEAR(@Date) + YEAR(@Date) / 4 + @PaschalDaysCalc - 13) % 7 
        ) 
 
        SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 
 
        SET @EasterDay = @NumOfDaysToSunday + 28 - ( 
            31 * (@EasterMonth / 4) 
        ) 
 
        SET @Date=(DATEADD(DAY,50,DATEADD(DAY,@EasterDay-1,DATEADD(month,@EasterMonth-1,CONVERT(DATE,CONVERT(VARCHAR(4),YEAR(@Date))))))) 
        IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Tuesday'
            SET @Date=DATEADD(day,6,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Wednesday'
            SET @Date=DATEADD(day,5,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Thursday'
            SET @Date=DATEADD(day,4,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Friday'
            SET @Date=DATEADD(day,3,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
            SET @Date=DATEADD(day,2,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,1,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'Whit Monday'
            END
    END
 
         
/* 
--------------------------
Late Summer Bank Holiday
--------------------------
Until 1971 the spring bank holiday was the first Monday in August, subsequently it was the last monday in August
*/
IF MONTH(@CheckDate) = 8
    BEGIN
        IF YEAR(@CheckDate) > 1970
            SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-08-25' ) 
        ELSE IF YEAR(@CheckDate) < 1971
            SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-08-01' ) 
 
        IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Tuesday'
            SET @Date=DATEADD(day,6,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Wednesday'
            SET @Date=DATEADD(day,5,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Thursday'
            SET @Date=DATEADD(day,4,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Friday'
            SET @Date=DATEADD(day,3,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Saturday'
            SET @Date=DATEADD(day,2,@Date)
        ELSE IF CONVERT(VARCHAR(9),DATENAME( dw, @Date )) = 'Sunday'
            SET @Date=DATEADD(day,1,@Date)
        IF (@Date = @CheckDate)
            BEGIN
                RETURN 'Late Summer Bank Holiday'
            END
    END
 
         
/* 
--------------------------
Good Friday
--------------------------
Friday immediately before Easter Sunday
*/
IF MONTH(@CheckDate) IN (3,4,5)
    BEGIN
        SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-01-01' ) 
 
            SET @EpactCalc = (24 + 19 * (YEAR(@Date) % 19)) % 30 
            SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 
            SET @NumOfDaysToSunday = @PaschalDaysCalc - ( 
                (YEAR(@Date) + YEAR(@Date) / 4 + @PaschalDaysCalc - 13) % 7 
            ) 
 
            SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 
 
            SET @EasterDay = @NumOfDaysToSunday + 28 - ( 
                31 * (@EasterMonth / 4) 
            ) 
 
          IF (DATEADD(DAY,-2,DATEADD(DAY,@EasterDay-1,DATEADD(month,@EasterMonth-1,CONVERT(DATE,CONVERT(VARCHAR(4),YEAR(@Date))))))) = @CheckDate
          BEGIN
            RETURN 'Good Friday'
          END
    END
 
         
/* 
--------------------------
Easter Monday
--------------------------
First Monday after Easter Sunday
*/
IF MONTH(@CheckDate) IN (3,4,5)
    BEGIN
        SET @Date=CONVERT( date, CONVERT(varchar, YEAR( @CheckDate ) )+'-01-01' ) 
 
            SET @EpactCalc = (24 + 19 * (YEAR(@Date) % 19)) % 30 
            SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 
            SET @NumOfDaysToSunday = @PaschalDaysCalc - ( 
                (YEAR(@Date) + YEAR(@Date) / 4 + @PaschalDaysCalc - 13) % 7 
            ) 
 
            SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44 
 
            SET @EasterDay = @NumOfDaysToSunday + 28 - ( 
                31 * (@EasterMonth / 4) 
            ) 
 
          IF (DATEADD(DAY,1,DATEADD(DAY,@EasterDay-1,DATEADD(month,@EasterMonth-1,CONVERT(DATE,CONVERT(VARCHAR(4),YEAR(@Date))))))) = @CheckDate
          BEGIN
            RETURN 'Easter Monday'
          END
END
 
 
         
/* 
--------------------------
Specials
--------------------------
Occasionally a day will be declared a bank holidy, primarily significant (Eye of beholder!) royal events
*/
IF @CheckDate = CONVERT(DATE,'2011-04-29')
    RETURN 'Special: Royal Wedding'
ELSE IF @CheckDate = CONVERT(DATE,'2002-06-05')
    RETURN 'Special: Golden Jubilee'   
ELSE IF @CheckDate = CONVERT(DATE,'2012-06-05')
    RETURN 'Special: Diamond Jubilee'  
ELSE IF @CheckDate = CONVERT(DATE,'1968-03-14')
    RETURN 'Emergency: Sterling Crisis'
 
         
/* 
--------------------------
Not every day can be special
--------------------------
Some days are just plain old days
*/
RETURN NULL
 
END

Feel free to use this as you see fit but I would recommend running some tests to ensure you are happy with the results as this has not been fully tested against all dates in the last 100 years.

I didn't do all the work and some fact checking and code came from the following sources

http://en.wikipedia.org/wiki/Public_holidays_in_the_United_Kingdom 
http://www.timeanddate.com/holidays/uk/ 
http://www.bankholidaydates.co.uk/GreatBritain.aspx 
http://stackoverflow.com/questions/457176/how-do-i-determine-a-public-holiday-in-sql-server 
http://lfhck.com/question/232569/function-to-return-date-of-easter-for-the-given-year (Not original source)

File download: usfGetBankHoliday.zip

Mike is MD & Principal Consultant for Damutu Ltd.

Mike has a particular interest in data analysis and supply chain optimisation. He has worked across a number of sectors including, finance, logistics, NGO, Start ups, Social Media and Government and has a broad knowledge of system integration.

Tag Cloud

Recent posts

UK Bank Holiday Function
3/14/2014 10:46 PM | Mike
SQL Server slowing start up of laptop
10/10/2013 11:47 PM | Mike
Contact

Give us a call today on 01865 366 154

or send us a message
Consultancy

- System Implementation
- Change Management
- Project / Programme Management
- Business Intelligence
- Interim Management
- Customs & Legal Compliance
- Solution Architecture
Applications

- Bespoke Application Design
    - Web Apps
    - Mobile Apps (IOS & Android)
    - Windows Applications
- Flexible Payment Terms
- Web Hosted Solutions
- OOH Support


Registered Address

11 Boundary Business Park
Wheatley Road
Garsington
Oxfordshire
OX44 9EJ
United Kingdom

Company Registration

Damutu Ltd. is a company registered in England and Wales with company number 08201615