More Oracle Dating Tips – High Days and Holidays

Following on from my post about Dates the other week, I’ve been looking around (afer all, there’s no harm in looking). There’s a fair number of clever date manipulation routines out there, calculating a business week, the tax year etc.
Work, work, work. What would be really useful is something that can work out when the Public Holidays are this year.

Now, for the most part, this is fairly simple. In England (as distinct from other parts of the UK), the public holidays pretty regular :

• May Day – the first Monday in May
• Spring Bank Holiday – the last Monday in May
• Summer Bank Holiday – the last Monday in August
• Christmas Bank Holiday – Christmas Day or the first Monday after if it falls on a weekend
• Boxing Day Bank Holiday – The day after the Christmas Bank Holiday or the first Monday after if Christmas Bank Holiday falls on a Friday.
• New Year Bank Holiday – New Year’s Day or the first Monday after if it falls on a weekend.

England is a peculiar nation. We have the obligatory Patron Saint as well as a reigning monarch. However, neither St George’s Day nor the Queen’s Birthday are public holidays. Incidentally, the Queen actually has two birthdays – her real one and her “official” one. Being a proud and loyal subject, I have no idea when either of these are.

All of which brings me on to someone else whose birthday ( or in this case, resurrection day) is not entirely straightforward.
Yes, the vexed question of Easter. Just how do they work it out ? Until recently, I suspected that the Pope just wrote out all of the Sundays between March 21st and April 25th, stuck them in a Cardinal’s hat, and drew one out. The truth however, is somewhat stranger.

Being – nominally at least – a Christian country, both Good Friday and Easter Monday are public holidays in the UK ( yes, Scotland, Ireland and Wales as well).

So, leaving aside the odd Royal Wedding, which may or may not be declared a Public Holiday depending on the importance of the Royal spouse-to-be ( or the number of times they’ve been married already), that’s our list of regular public holidays. Now all we need is a handy little program to work out when they are.

VARCHAR2, you want spaces with that ?

Before I go any further into the bank holiday programming fun and frolics, there is something a little bit odd about the TO_CHAR function.
All the Oracle manuals insist that it will always return a VARCHAR2 so…

set serveroutput on size 1000000 
DECLARE 
    l_silly VARCHAR2(10); 
BEGIN 
    l_silly := 'SILLY'; 
    DBMS_OUTPUT.PUT_LINE(l_silly||' Length of '||LENGTH( l_silly)); 
    l_silly := TO_CHAR(123456); 
    DBMS_OUTPUT.PUT_LINE(l_silly||' Length of '||LENGTH( l_silly));    
END; 
/

SILLY Length of 5 
123456 Length of 6 

PL/SQL procedure successfully completed. 

However, if you want to get the day of the week back….

set serveroutput on size 1000000 
DECLARE 
    l_silly VARCHAR2(10); 
    l_date DATE := TO_DATE('30-07-1966', 'DD-MM-RRRR'); 
BEGIN 
    l_silly := TO_CHAR(l_date, 'DAY'); 
    DBMS_OUTPUT.PUT_LINE(l_silly||' Length of '||LENGTH( l_silly));    
END; 
/

SATURDAY  Length of 9 

PL/SQL procedure successfully completed. 

Irrespective of the day of the week returned, the return value is always padded to 9 characters. Obviously, Wednesday being the longest name of a day is 9 characters which would explain why the string is that length. However you don’t normally have odd spaces loitering at the end of a VARCHAR2 unless you put them there yourself. Incidentally, the same thing happens with months.

In any case, if you want to run through some logic with a DAY value, it’s best to use the TRIM function.

set serveroutput on size 1000000 
DECLARE 
    l_silly VARCHAR2(10); 
    l_date DATE := TO_DATE('30-07-1966', 'DD-MM-RRRR'); 
BEGIN 
    l_silly := TO_CHAR(l_date, 'DAY'); 
    IF l_silly = 'SATURDAY' THEN 
        DBMS_OUTPUT.PUT_LINE('They think its all over'); 
    ELSE 
        DBMS_OUTPUT.PUT_LINE('Bobby who ?'); 
    END IF; 
    IF TRIM( l_silly) = 'SATURDAY' THEN 
        DBMS_OUTPUT.PUT_LINE('How many years of hurt is that ?'); 
    ELSE 
        DBMS_OUTPUT.PUT_LINE('This program was written by a Scot.'); 
    END IF; 
END; 
/

Bobby who ? 
How many years of hurt is that ? 

PL/SQL procedure successfully completed. 

Yes, as this is a particularly Anglo-centric post, I thought it only fitting to hark back to the day on which England won the World Cup.

Of course, a better solution would be to use the three-letter abbreviation ( using ‘DY’) which, predictably, is always three characters long.

Christmas and New Year – the easy ones

These are the easiest to calculate because they are all based around a given date…

set serveroutput on size 1000000 
DECLARE 
    l_nyd VARCHAR2(3); 
    l_xmas_day VARCHAR2(3); 
    l_ny_date DATE := TO_DATE('01-01-11', 'DD-MM-RR'); 
    l_xmas_date DATE := TO_DATE('25-12-11', 'DD-MM-RR'); 
    l_bd_date DATE := TO_DATE('26-12-11', 'DD-MM-RR'); 
BEGIN 
    l_nyd := TO_CHAR( l_ny_date, 'DY'); 
    l_ny_date := l_ny_date + 
        CASE l_nyd 
            WHEN 'SAT' THEN 2 
            WHEN 'SUN' THEN 1 
            ELSE 0 
        END; 
    l_xmas_day := TO_CHAR( l_xmas_date, 'DY'); 
    l_xmas_date := l_xmas_date + 
        CASE l_xmas_day 
            WHEN 'SAT' THEN 2 
            WHEN 'SUN' THEN 1 
            ELSE 0 
        END; 
    l_bd_date := l_xmas_date + 
        CASE TO_CHAR(l_xmas_date, 'DY') 
            WHEN 'FRI' THEN 3 
            ELSE 1 
        END; 
    DBMS_OUTPUT.PUT_LINE('New Year Holiday is '||TO_CHAR(l_ny_date,'DD-MM-RR')); 
    DBMS_OUTPUT.PUT_LINE('Christmas Holiday is '||TO_CHAR(l_xmas_date,'DD-MM-RR')); 
    DBMS_OUTPUT.PUT_LINE('Boxing Day Holiday is '||TO_CHAR(l_bd_date,'DD-MM-RR'));        
END; 
/

Mayday, mayday

As we emerge from the dark depths of winter, new year’s hangovers just a distant memory, leave allocation almost exhausted…we really need an emergency holiday. Step forward the month of May…

set serveroutput on size 1000000 
DECLARE 
    l_mayday DATE := TO_DATE('01-05-11', 'DD-MM-RR'); 
    l_spring DATE := TO_DATE('31-05-11', 'DD-MM-RR'); 
BEGIN 
    l_mayday := l_mayday + 
        CASE TO_CHAR( l_mayday, 'DY') 
            WHEN 'TUE' THEN 6 
            WHEN 'WED' THEN 5 
            WHEN 'THU' THEN 4 
            WHEN 'FRI' THEN 3 
            WHEN 'SAT' THEN 2 
            WHEN 'SUN' THEN 1 
            ELSE 0 
        END; 
    l_spring := l_spring - 
        CASE TO_CHAR( l_spring, 'DY') 
            WHEN 'TUE' THEN 1 
            WHEN 'WED' THEN 2 
            WHEN 'THU' THEN 3 
            WHEN 'FRI' THEN 4 
            WHEN 'SAT' THEN 5 
            WHEN 'SUN' THEN 6 
            ELSE 0 
        END; 
    DBMS_OUTPUT.PUT_LINE('May Day Holiday is on ' 
        ||TO_CHAR( l_mayday, 'DD-MM-RR')); 
    DBMS_OUTPUT.PUT_LINE('Spring Bank Holiday is on ' 
        ||TO_CHAR( l_spring, 'DD-MM-RR'));        
END; 
/

Easter Lunacy

This section is entitled lunacy, not so much for the archaic and arcane method of calculating when Easter should fall as for the fact that it’s based around the movement of an abstract concept known as the Ecclesiastical Moon.
After some in-depth research involving Golden Numbers, Julian and Gregorian calendars, and The Council of Nicea, I still couldn’t work out where the Eggs and the Rabbits fitted in. Therefore, I decided to abandon any attempt to understand how this all fitted together and copy someone else’s algorithm.
So, with humble thanks to the sometime Bishop of Meath, here’s the PL/SQL version…

set serveroutput on size 1000000
DECLARE
    a PLS_INTEGER;
    b PLS_INTEGER;
    c PLS_INTEGER;
    d PLS_INTEGER;
    e PLS_INTEGER;
    f PLS_INTEGER;
    g PLS_INTEGER;
    h PLS_INTEGER;
    i PLS_INTEGER;
    k PLS_INTEGER;
    l PLS_INTEGER;
    m PLS_INTEGER;
    p PLS_INTEGER;
    l_month PLS_INTEGER;
    l_day PLS_INTEGER;
    l_year PLS_INTEGER := 2011;

    l_easter_day DATE;
    l_good_friday DATE;
    l_easter_monday DATE;
BEGIN
  a := MOD( l_year, 19);
  b := FLOOR(l_year/100);
  c := MOD(l_year,100);
  d := FLOOR(b/4);
  e := MOD(b,4);
  f := FLOOR((b+8)/25);
  g := FLOOR((b-f+1)/3);
  h := MOD((19*a+b-d-g+15),30);
  i := FLOOR(c/4);
  k := MOD(c,4);
  l := MOD((32+2*e+2*i-h-k),7);
  m := FLOOR((a+11*h+22*l)/451);
  l_month := FLOOR((h+l-7*m+114)/31);  -- 3=March, 4=April
  p := MOD((h+l-7*m+114),31);
  l_day := p+1 ;  -- date in Easter Month
dbms_output.put_line('Day = '||l_day||'Month = '||l_month);
l_easter_day := TO_DATE(l_day||'-'||l_month||'-'||l_year, 'DD-MM-RR');
dbms_output.put_line('Easter is on '||TO_CHAR( l_easter_day, 'DD-MM-RR'));
l_good_friday := l_easter_day - 2;
l_easter_monday := l_easter_day + 1;
DBMS_OUTPUT.PUT_LINE('Good Friday is on '||TO_CHAR( l_good_friday, 'Day DDth Month'));
DBMS_OUTPUT.PUT_LINE('Easter Monday is on '||TO_CHAR( l_easter_monday, 'Day DDth Month'));
END;
/

A feast of festivities

For those days when you’re beginning to flag, or simply want to make the most of a long weekend afforded by a Public Holiday, you could always run this little script to help you plan ahead…

set verify off 
accept year prompt 'Enter the year in format YYYY : ' 
set serveroutput on size 1000000 
DECLARE 
    l_year NUMBER(4) := '&year'; 
    l_nyd DATE; 
    l_easter_sun DATE; 
    l_good_fri DATE; 
    l_easter_mon DATE; 
    l_mayday DATE; 
    l_spring DATE; 
    l_summer DATE; 
    l_xmas DATE; 
    l_boxing DATE; 
    
    FUNCTION get_next_mon_fn( p_date IN DATE) 
        RETURN DATE 
    IS 
    BEGIN 
        RETURN p_date + 
            CASE TO_CHAR( p_date, 'DY') 
                WHEN 'TUE' THEN 6 
                WHEN 'WED' THEN 5 
                WHEN 'THU' THEN 4 
                WHEN 'FRI' THEN 3 
                WHEN 'SAT' THEN 2 
                WHEN 'SUN' THEN 1 
                ELSE 0 
            END; 
    END get_next_mon_fn; 
    
    FUNCTION get_last_mon_fn( p_date IN DATE) 
        RETURN DATE 
    IS 
    BEGIN 
        RETURN p_date - 
            CASE TO_CHAR( p_date, 'DY') 
                WHEN 'TUE' THEN 1 
                WHEN 'WED' THEN 2 
                WHEN 'THU' THEN 3 
                WHEN 'FRI' THEN 4 
                WHEN 'SAT' THEN 5 
                WHEN 'SUN' THEN 6 
                ELSE 0 
            END; 
    END get_last_mon_fn; 
    
    FUNCTION get_easter_day_fn( p_year IN NUMBER) 
        RETURN DATE 
    IS 
        a PLS_INTEGER; 
        b PLS_INTEGER; 
        c PLS_INTEGER; 
        d PLS_INTEGER; 
        e PLS_INTEGER; 
        f PLS_INTEGER; 
        g PLS_INTEGER; 
        h PLS_INTEGER; 
        i PLS_INTEGER; 
        k PLS_INTEGER; 
        l PLS_INTEGER; 
        m PLS_INTEGER; 
        p PLS_INTEGER; 
        l_month PLS_INTEGER; 
        l_day PLS_INTEGER; 

        l_easter_day DATE; 
    BEGIN 
        a := MOD( l_year, 19); 
        b := FLOOR(l_year/100); 
        c := MOD(l_year,100); 
        d := FLOOR(b/4); 
        e := MOD(b,4); 
        f := FLOOR((b+8)/25); 
        g := FLOOR((b-f+1)/3); 
        h := MOD((19*a+b-d-g+15),30); 
        i := FLOOR(c/4); 
        k := MOD(c,4); 
        l := MOD((32+2*e+2*i-h-k),7); 
        m := FLOOR((a+11*h+22*l)/451); 
        l_month := FLOOR((h+l-7*m+114)/31);  -- 3=March, 4=April 
        p := MOD((h+l-7*m+114),31); 
        l_day := p+1 ;  -- date in Easter Month 
        l_easter_day := TO_DATE(l_day||'-'||l_month||'-'||l_year, 'DD-MM-RR'); 
        RETURN l_easter_day;    
    END get_easter_day_fn;    
    
BEGIN 
    -- 
    -- Initialise the dates that are constant 
    -- 
    l_nyd := TO_DATE('01-01-'||TO_CHAR(l_year), 'DD-MM-YYYY'); 
    l_mayday := TO_DATE('01-05-'||TO_CHAR(l_year), 'DD-MM-YYYY'); 
    l_spring := TO_DATE('31-05-'||TO_CHAR(l_year), 'DD-MM-YYYY'); 
    l_summer := TO_DATE('31-08-'||TO_CHAR(l_year), 'DD-MM-YYYY'); 
    l_xmas := TO_DATE('25-12-'||TO_CHAR(l_year), 'DD-MM-YYYY'); 
    l_boxing := l_xmas + 1; 
    -- 
    -- Find the "constant" holidays 
    -- 
    IF TO_CHAR(l_nyd, 'DY') IN ('SAT', 'SUN') THEN 
        l_nyd := get_next_mon_fn( l_nyd); 
    END IF; 
    IF TO_CHAR( l_mayday, 'DY') IN ('SAT', 'SUN') THEN 
        l_mayday := get_next_mon_fn( l_mayday); 
    END IF; 
    IF TO_CHAR( l_spring, 'DY') != 'MON' THEN 
        l_spring := get_last_mon_fn( l_spring); 
    END IF; 
    IF TO_CHAR( l_summer, 'DY') != 'MON' THEN 
        l_summer := get_last_mon_fn( l_summer); 
    END IF; 
    IF TO_CHAR( l_xmas, 'DY') IN ('SAT', 'SUN') THEN 
        l_xmas := get_next_mon_fn( l_xmas); 
    END IF; 
    IF TO_CHAR( l_xmas, 'DY') = 'FRI' THEN 
        l_boxing := get_next_mon_fn(l_xmas); 
    ELSE 
        l_boxing := l_xmas + 1; 
    END IF; 
    -- 
    -- Now get the Easter Dates 
    -- 
    l_easter_sun := get_easter_day_fn( l_year); 
    l_easter_mon := l_easter_sun + 1; 
    l_good_fri := l_easter_sun -2; 
    -- 
    -- Now print out all the holidays 
    -- 
    DBMS_OUTPUT.PUT_LINE('Public Holidays in England for '||l_year); 
    DBMS_OUTPUT.PUT_LINE('-----------------------------------'); 
    DBMS_OUTPUT.PUT_LINE('New Years Day Holiday is on ' 
        ||TO_CHAR(l_nyd, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Good Friday is on ' 
        ||TO_CHAR( l_good_fri, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Easter Monday is on ' 
        ||TO_CHAR( l_easter_mon, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('May Day Bank Holiday is on ' 
        ||TO_CHAR( l_mayday, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Spring Bank Holiday is on ' 
        ||TO_CHAR( l_spring, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Summer Bank Holiday is on ' 
        ||TO_CHAR( l_summer, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Christmas Day Holiday is on ' 
        ||TO_CHAR( l_xmas, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('Boxing Day is on ' 
        ||TO_CHAR( l_boxing, 'DDTH Month YYYY')); 
    DBMS_OUTPUT.PUT_LINE('May your weekends be long!.'); 
END; 
/        
set verify on

At this point I’m going to run away quickly before Deb starts asking about the number of shopping days to Christmas.