SQLServerCentral Article

Politics makes for bad databases - plus calculate British holidays


I have recently been reflecting on how politics frequently adversely affects databases, and is the cause of change. It might seem obvious to expand this concept to reflect on how politics seems to adversely affect many other things, but this is a place to consider databases, and not wider political shenanigans.

Over the years, I have seen how database design can be severely hampered by internal politics. Some departments may ‘own’ some data, and this is jealously guarded, to the detriment of whole-company systems. Frequently, the ideal solution has to defer to vested interests from some quarters. The location of hardware is less important today than in the past, but for multi-site situations, the hardware may have to go where it is politically deemed necessary, rather than locating it for the optimum practicality and function.

I had some dealings with a small manufacturing company, and the boss there had been told by his golfing buddies of how wonderful SAP was, so he wanted to implement it. SAP is no doubt a great product, but it is perhaps not the obvious choice for a company with 20 employees, which faced some pretty serious problems. It was crazy that the main effort from the staff for a period of time had to be to dissuade the boss from his intended course of self-destruction.

I don’t doubt for a moment that many of you will have tales of woe that would keep us all enthralled for ages.

In recent years, GDPR has had a huge impact on databases, and continues to do so. I am not going to debate the rights or wrongs of it, but it is a very certain reality for databases today. What isn’t certain yet is what it actually all means, and we won’t get too much of a handle on that until the first prosecutions have taken place. What is very clear is that nobody wants to be among those particular trail blazers, which is why there is intense activity to harden systems.

As I write this, just this past week, we in the UK have had notice that our May Day bank holiday next year is changing. The reason for this is to celebrate the 75th anniversary of VE day, which for the uninitiated is Victory in Europe Day. This is a big deal, and had there been no original VE day, we would have had no EU, and therefore no GDPR. And no need for the omni-shambles that is Brexit.  But thinking that no original VE day would have been better is a dangerous path to travel, and not within the scope of a database discussion.

For the average Joe, having the date of the May Day bank holiday change from Monday 4th May to Friday 8th May is no big deal. However, Calendar and Diary manufacturers have already printed much of next year’s stock, and now they have to change all that they have done, and that is going to be expensive. Calendars and diaries are printed and bound by machines, but correcting the already printed stock will have to be done by hand, and it needs to be done very soon. Calendars will probably be fixed by reprinting the May page, and swapping the old page for a new one. Diaries may end up with labels being stuck over the two days, or the whole page, depending on how the diary is formatted.

However it is done, it will be a huge hassle, and financially stressful for those companies. There is some precedent for changing this date, as it was changed in 1995 to celebrate the 50th anniversary. But as one Calendar manufacturer pointed out, the 1995 change was announced in 1993, and gave much more warning than the current 11 month warning of next year’s change. Don’t count on there being no change in 2045!

This all brings us rather neatly to the real reason for this article. Three years ago I wrote a function to calculate UK Holidays, and this now needs a tweak to accommodate this recently announced change. The Easter calculation is the most complex aspect of this function, and for this I relied on previous giants of logic who created this fine T-SQL code. The rest of the function is very straightforward, just follow the well-publicised rules, cater for periodic quirks, and you get the right result.

I have attached the code to create the function - Create_fnGetUKHolidays

Getting the dates for the UK holidays in a particular year is done like this:-

Select * From dbo.fnGetUKHolidays(2020);

And that gives the following result:-

1New Years Day2020-01-01
2Good Friday2020-04-10
3Easter Sunday2020-04-12
4Easter Monday2020-04-13
5May Day Bank Holiday2020-05-08
6Spring Bank Holiday2020-05-25
7Summer Bank Holiday2020-08-31
8Christmas Day2020-12-25
9Boxing Day2020-12-26
10Boxing Day Bank Holiday2020-12-28

The reason that I created this function was because I was fixing a problem with adding periods of time, and created a Date Dimension as part of this. Please do check out the article that I wrote about that fix - Adding Years, Months and Days


3.4 (5)




3.4 (5)