HANA Tips & Tricks: issue #2 – Calculating Easter and related holidays with a HANA scalar function

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:

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:

ASH_WEDNESDAY MAUNDY_THURSDAY GOOD_FRIDAY EASTER_DAY ASCENSION_THURSDAY PENTECOST
10-feb-2016 24-mrt-2016 25-mrt-2016 27-mrt-2016 5-mei-2016 15-mei-2016
1-mrt-2017 13-apr-2017 14-apr-2017 16-apr-2017 25-mei-2017 4-jun-2017
14-feb-2018 29-mrt-2018 30-mrt-2018 1-apr-2018 10-mei-2018 20-mei-2018
6-mrt-2019 18-apr-2019 19-apr-2019 21-apr-2019 30-mei-2019 9-jun-2019
26-feb-2020 9-apr-2020 10-apr-2020 12-apr-2020 21-mei-2020 31-mei-2020
17-feb-2021 1-apr-2021 2-apr-2021 4-apr-2021 13-mei-2021 23-mei-2021
2-mrt-2022 14-apr-2022 15-apr-2022 17-apr-2022 26-mei-2022 5-jun-2022
22-feb-2023 6-apr-2023 7-apr-2023 9-apr-2023 18-mei-2023 28-mei-2023
14-feb-2024 28-mrt-2024 29-mrt-2024 31-mrt-2024 9-mei-2024 19-mei-2024
5-mrt-2025 17-apr-2025 18-apr-2025 20-apr-2025 29-mei-2025 8-jun-2025

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!

This article belongs to
Tags
  • Calculating Easter and related holidays with a HANA scalar function
  • HANA easter calc
  • HANA scalar function
Author
  • Roland Bouman