Solution: https://stackoverflow.com/questions/260 ... ter-sunday describes how to calculate Easter Sunday for any given year number in 10 simple steps. I transfered these simple functions to Calc and substituted the variables until I got a month formula depending on the year only and a day formula depending on month and year only.
Caveat: With the embedded HSQL1.8 some dates differ by -7 days. This seems to be a bug in HSQL1.8. With HSQL2.3.4 and 2.4.x the query results for all years between 1584 and 2100 are identical to Calc's EASTERSUNDAY.
The uploaded zip archive http://www.mediafire.com/file/9979c8ru5 ... undays.zip contains my test database with HSQL2.4.1 driver and a Base document with auto-install macro. Extract to a trusted directory and open EasterSundays.odb. The auto-install macro should report a successfull connection of the document with database and driver. Two issues with the HSQL 2.4.1 driver and workarounds are described in a README form embedded in the database.
Having a list of year numbers "Years"."Y", I calculate month and day numbers like this:
Code: Select all
SELECT "Y", "M", <day formula> AS "D" FROM (SELECT "Y", <month formula> AS "M" FROM "Years") AS "Months"
Code: Select all
SELECT "Y", "M", <day formula> AS "D" FROM (SELECT "Y", <month formula> AS "M" FROM (SELECT DISTINCT YEAR("Dates") AS "Y" FROM "Table") AS "Years") AS "Months"
Code: Select all
FLOOR((MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) - FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + MOD(2 * MOD(FLOOR("Y"/100),4) + 2 * FLOOR(MOD("Y",100)/4) - MOD(MOD("Y",100),4) - MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) + FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + 32,7) + 90)/25)
Code: Select all
MOD(MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) - FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + MOD(2 * MOD(FLOOR("Y"/100),4) + 2 * FLOOR(MOD("Y",100)/4) - MOD(MOD("Y",100),4) - MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) + FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + 32,7) + "M" + 19,32)
Save the working query as a view because in this particular case you can do more with a view.
In the example database I UNION merged some variable christian holidays (Good Friday, Easter, Pentecost, Corp. Chr.) with a list of fixed holydays (national, Xmas etc.):
Code: Select all
SELECT dateadd('day',-3,CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE))AS "DT", 'Good Friday' AS "DESCRIPTION" FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE), 'Easter Sunday' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day',39, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Ascension Day' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day', 49, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Pentecost Sunday' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day', 60, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Corpus Christ (cath.)' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT CAST("vEasterSundays"."Y" ||'-'|| "FH"."M" ||'-'|| "FH"."D" AS DATE), DESCR FROM "FixedHolydays" AS "FH", "vEasterSundays" WHERE NOT "FH"."IGN" AND "Y" BETWEEN 2010 AND 2018