Oracle – for when it was like that when you got there

Main menu

Post navigation

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…

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…

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…

spot on, FMDay doesn’t have the trailing space issue. However, I thought it worth mentioning about the DAY and MONTH format specifiers as, ultimately, you’re using TO_CHAR and would have, I think, a reasonable expectation of getting a VARCHAR2 back.