At Just-BI we recently launched a monthly knowledge-sharing initiative where our consultants and developers discuss any issues and share tips & tricks concerning SAP HANA development. We share any insights here on SCN. About a month ago we published our first post: HANA Tips & Tricks: issue #1 – Hacking information views

We just had our second HANA open Mic session, and we decided to start publishing one post for each topic. In this post, I will explain how to calculate the date of easter for any given year. Please also check out other posts in this month’s series:

• HANA Tips & Tricks: Issue #2a – Digging into Table Variables by Just-Anisha

Calculating Easter Day

In today’s post, I want to share a SAP HANA scalar function that calculates easter day for a given year. Many countries throughout the world observe a number of holidays which can all be derived from easter day. Being able to calculate these holidays for arbitrary years is useful for any planning or calendaring application, as well as for analytical applications. For example, one could adorn a date dimension table with attributes that indicate whether a particular date is a holiday, and what type of holiday it is. In such cases, a function that calculates easter day will come in handy to compute at least some of the holidays.

Unlike for example Christmas and New Year’s day, Easter is a so-called moveable feast – its date is not fixed, but changes from one year to the next. In many cases this is caused by the fact that some component of the date calculation relies on a moon calendar. This is also the case with easter. If you’re interested in the background and details of the calculation, wikipedia has an excellent article on this topic.

The wikipedia article also offers a number of algorithms to do the actual calculation. Since medieval times, these calculations are known as “Computus”, which simply mean “Calculation”. I lifted one particular algorithm from the wikpedia article, the so-called Anonymous Gregorian Algorithm, and wrote it as a SAP/HANA scalar function. It goes like this:

CREATE FUNCTION f_easter_day (
  p_year smallint 
) 
RETURNS p_easter_date date
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
BEGIN
  declare v_year smallint default ifnull(p_year, year(current_date));
  DECLARE a SMALLINT DEFAULT mod(v_year, 19);
  DECLARE b SMALLINT DEFAULT FLOOR(v_year / 100);
  DECLARE c SMALLINT DEFAULT mod(v_year, 100);
  DECLARE d SMALLINT DEFAULT FLOOR(b / 4);
  DECLARE e SMALLINT DEFAULT mod(b, 4);
  DECLARE f SMALLINT DEFAULT FLOOR((b + 8) / 25);
  DECLARE g SMALLINT DEFAULT FLOOR((b - f + 1) / 3);
  DECLARE h SMALLINT DEFAULT mod((19 * a + b - d - g + 15), 30);
  DECLARE i SMALLINT DEFAULT FLOOR(c / 4);
  DECLARE k SMALLINT DEFAULT mod(c, 4);
  DECLARE L SMALLINT DEFAULT mod((32 + 2 * e + 2 * i - h - k), 7);
  DECLARE m SMALLINT DEFAULT FLOOR((a + 11 * h + 22 * L) / 451);
  DECLARE v100 SMALLINT DEFAULT h + L - 7 * m + 114;
  p_easter_date = to_date(
                      v_year||'-'||floor(v100 / 31)||'-'||(mod(v100, 31)+1)
                  ,   'YYYY-MM-DD'
                  );  
END;

As you can see, the function f_easter_day takes a p_year parameter, which is an SMALLINT representing the year for which you’d like to calculate easter day in that year. The return value is a DATE which represents the actual date that marks easter sunday in the given year.

The code is positively obscure – I do not pretend I can explain exactly how this works. However, I have tested it and so far the function really does seem to work.If you are interested in how it works, then please review the wikipedia article.

Let’s Calculate some Holidays!

The following code sample illustrates how to calculate easter day, as well as a number of holidays that may be directly derived from easter day:

• Ash Wednesday (End of Carnival – Start of Lent)
• Mandy Thursday
• Good Friday
• Ascension
• Pentecost

with easter_days as 
(
  select  f_easter_day(generated_period_start) easter_day  
  from    series_generate_smallint(1, year(current_date), year(current_date) + 10)
)
select  add_days(easter_day, -46)   ash_wednesday
,       add_days(easter_day, -3)    maundy_thursday
,       add_days(easter_day, -2)    good_friday    
,       easter_day              
,       add_days(easter_day, 39)    ascension_thursday
,       add_days(easter_day, 49)    pentecost
from    easter_days

As you can see, we use the SAP HANA table function SERIES_GENERATE_SMALLINT()to generate a set of integers, starting from the current year, and ending 10 years from now. The result looks like:

Finally

I hope you enjoyed this post and I hope you’ll find the f_easter_day() function useful! If you have some tips on calculating holidays, or if you have alternative ways to calculate easter day, then by all means – leave a comment to discuss! We love your feedback.

In the mean while, stay tuned for more SAP HANA tips and tricks by searching for the hanatipsandtricks hashtag on SCN. Thank you for your time!