Calculate statutory public holidays for Victoria Australia
This is not a question, it's a post. I just had to write this for work and don't want it to go to waste. The following is a PL/SQL script to generate statutory public holidays in the state of Victoria, Australia according to the Public Holidays Act 1993. It creates a table and 2 functions. The first calculates Easter Sunday based on an algorithm from http://www.assa.org.au/edm.html. The second generates all other public holidays and inserts them into the table. Please feel free to copy and port to any language. However, while I have unit tested this myself, please don't threaten to sue me if you use this without performing thorough testing yourself.

I hope this helps someone.

		DROP TABLE ADMIN_PUBLIC_HOLIDAYS;
		drop function calculate_easter_sunday;
		DROP PROCEDURE GENERATE_PUBLIC_HOLIDAYS;


		CREATE TABLE ADMIN_PUBLIC_HOLIDAYS (
		  HOLIDAY_DATE                DATE,
		  HOLIDAY_NAME                VARCHAR2(50) )
		TABLESPACE xxx_huge
		LOGGING
		STORAGE ( INITIAL 1M NEXT 1M )
		;
		/
		CREATE UNIQUE INDEX ADMIN_PUBLIC_HOLIDAYS_IDX1 ON 
		  ADMIN_PUBLIC_HOLIDAYS (HOLIDAY_DATE) 
		  TABLESPACE xxx_BIG_IX 
		  STORAGE(INITIAL 500k NEXT 250k ) 
		; 

		/

		CREATE OR REPLACE
		FUNCTION calculate_easter_sunday(
			  pn_year NUMBER) return date
		  IS
		/***************************************************
		  Name:         calculate_easter_sunday
		  Created Date: 01/5/2012
		  Author:       Vivian Farrell
		  Input:        Year
		  Output:       Date of easter sunday

		  Description:  This function has been ported from the
		  BASIC code available from http://www.assa.org.au/edm.html.
		  The dates that are generated are used by the Australian
		  Government to calculate the easter period each year.
		****************************************************/
			ln_day       NUMBER;
			ln_month     NUMBER;
			ln_first_dig NUMBER;
			ln_remain_19 NUMBER;
			ln_temp      NUMBER;
			ln_tA        NUMBER;
			ln_tB        NUMBER;
			ln_tC        NUMBER;
			ln_tD        NUMBER;
			ln_tE        NUMBER;
			lv_tmp      varchar(50);
		  BEGIN
			ln_first_dig := floor(pn_year / 100);    -- first 2 digits of year
			ln_remain_19 := mod (pn_year,19); --remainder of year / 19
			-- calculate PFM date
			ln_temp := floor((ln_first_dig - 15) / 2) + 202 - 11 * ln_remain_19;
			 
			if ( ln_first_dig in (21, 24, 25, 27,28,29,30,31,32, 34, 35, 38) )THEN 
			  ln_temp := ln_temp - 1;
			elsif( ln_first_dig IN (33, 36, 37, 39, 40) ) THEN
			  ln_temp    := ln_temp - 2;
			end if;
			
			ln_temp := Mod(ln_temp, 30);
			ln_tA       := ln_temp + 21;
			IF (ln_temp  = 29) THEN
			  ln_tA     := ln_tA - 1;
			END IF;
			IF (ln_temp = 28 AND ln_remain_19 > 10) THEN
			  ln_tA    := ln_tA - 1;
			END IF;
			--find the next Sunday
			ln_tB   := Mod((ln_tA - 19),7);
			ln_tC   := Mod((40    - ln_first_dig), 4);
			IF ln_tC = 3 THEN
			  ln_tC := ln_tC + 1;
			END IF;
			IF ln_tC > 1 THEN
			  ln_tC := ln_tC + 1;
			END IF;
			ln_temp := Mod(pn_year, 100);
			ln_tD   := Mod (floor(ln_temp + ln_temp / 4),7);
			ln_tE   := Mod((20       - ln_tB - ln_tC - ln_tD), 7) + 1;
			ln_day  := ln_tA         + ln_tE;
			--return the date
			IF ln_day       > 31 THEN
			  ln_day       := ln_day - 31;
			  ln_month     := 4;
			ELSE
			  ln_month := 3;
			END IF;
			lv_tmp := concat(concat(lpad(ln_day,2,'0'),lpad(ln_month,2,'0')),pn_year);
			RETURN to_date(lv_tmp,'DDMMYYYY');
		  END;

		/
		create or replace
		PROCEDURE Generate_Public_Holidays(start_year number, end_year number)
		IS
		/***************************************************
		  Name:         Generate_Public_Holidays
		  Created Date: 01/5/2012
		  Author:       Vivian Farrell
		  Input:        start year, end year
		  Output:       ADMIN_PUBLIC_HOLIDAYS updated
		  Dependencies: calculate_easter_sunday function

		  Description:  This procedure calculates all Victorian
		  public holidays according to the Public Holidays Act
		  1993 and populates them into the ADMIN_PUBLIC_HOLIDAYS
		  table.
		****************************************************/
		  tempday VARCHAR2(50);
		  nydate DATE;
		  ausdate DATE;
		  labdate DATE;
		  easter_friday DATE;
		  easter_monday date;
		  anzacdate DATE;
		  queendate DATE;
		  cupdate date;
		  chrisdate DATE;
		  boxingdate date;
		  
		BEGIN

		  FOR i IN start_year..end_year loop
			---- New Years Day - 1st Jan
			tempday := to_char(to_Date(concat('01-JAN-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY') THEN
			  nydate := to_date(concat('03-JAN-',i),'DD-MON-YYYY');
			ELSIF (trim(tempday) = 'SUNDAY') THEN
			  nydate := to_date(concat('02-JAN-',i),'DD-MON-YYYY');
			ELSE
			  nydate := to_date(concat('01-JAN-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (nydate,'NEW YEARS DAY');

			-- Australia Day - 26th Jan
			tempday := to_char(to_Date(concat('26-JAN-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY') THEN
			  ausdate := to_date(concat('28-JAN-',i),'DD-MON-YYYY');
			ELSIF (trim(tempday) = 'SUNDAY') THEN
			  ausdate := to_date(concat('27-JAN-',i),'DD-MON-YYYY');
			ELSE
			  ausdate := to_date(concat('26-JAN-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (ausdate,'AUSTRALIA DAY');

			-- Labour Day - 2nd Monday of March
			labdate := 
				next_day(
				  to_char(
					next_day(
					  to_char(
						last_day(
						  concat('01-FEB-',i)
						)
					  ,'DD/MON/YYYY')
					,'MONDAY')
				  ,'DD/MON/YYYY')
				,'MONDAY');
				
			insert into ADMIN_PUBLIC_HOLIDAYS values (labdate,'LABOUR DAY');
			
			-- Easter Friday
			easter_friday := calculate_easter_sunday(i) - 2;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (easter_friday,'EASTER FRIDAY');

			-- Easter Monday
			easter_monday := calculate_easter_sunday(i) + 1;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (easter_monday,'EASTER MONDAY');
			
			-- ANZAC day - 25th April
			anzacdate := to_date(concat('25-APR-',i),'DD-MON-YYYY');
			-- if anzac day falls on easter monday
			IF (to_char(easter_monday,'DD-MM-YYYY') = to_char(anzacdate, 'DD-MM-YYYY')) THEN
			  anzacdate := anzacdate + 1;
			END IF;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (anzacdate,'ANZAC DAY');
			
			-- Queens Birthday - 2nd Monday in June
			queendate := 
				next_day(
				  to_char(
					next_day(
					  to_char(
						last_day(
						  concat('31-MAY-',i)
						)
					  ,'DD/MON/YYYY')
					,'MONDAY')
				  ,'DD/MON/YYYY')
				,'MONDAY');
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (queendate,'QUEENS BIRTHDAY');
			
			-- Melbourne Cup day - 1st Tuesday in November
			cupdate := 
					next_day(
					  to_char(
						last_day(
						  concat('31-OCT-',i)
						)
					  ,'DD/MON/YYYY')
					,'TUESDAY');
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (cupdate,'MELBOURNE CUP DAY');
			
			-- Christmas day - 25th or 27th if on weekend
			tempday := to_char(to_Date(concat('25-DEC-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY' OR trim(tempday) = 'SUNDAY') THEN
			  chrisdate := to_date(concat('27-DEC-',i),'DD-MON-YYYY');
			ELSE
			  chrisdate := to_date(concat('25-DEC-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (chrisdate,'CHRISTMAS DAY');
			
			-- Boxing day - 26th or 28th if on weekend
			tempday := to_char(to_Date(concat('26-DEC-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY' OR trim(tempday) = 'SUNDAY') THEN
			  boxingdate := to_date(concat('28-DEC-',i),'DD-MON-YYYY');
			ELSE
			  boxingdate := to_date(concat('26-DEC-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (boxingdate,'BOXING DAY');
			
		  
		  END loop;
		END;

		/

		DECLARE
		  START_YEAR NUMBER;
		  END_YEAR NUMBER;
		BEGIN
		  START_YEAR := 1993; -- start when the Act was brought in
		  END_YEAR := 2100; -- should be enough for now.

		  GENERATE_PUBLIC_HOLIDAYS(
			START_YEAR => START_YEAR,
			END_YEAR => END_YEAR
		  );
		END;


Calculate statutory public holidays for Victoria Australia
===

This is not a question, it's a post. I just had to write this for work and don't want it to go to waste. The following is a PL/SQL script to generate statutory public holidays in the state of Victoria, Australia according to the Public Holidays Act 1993. It creates a table and 2 functions. The first calculates Easter Sunday based on an algorithm from http://www.assa.org.au/edm.html. The second generates all other public holidays and inserts them into the table. Please feel free to copy and port to any language. However, while I have unit tested this myself, please don't threaten to sue me if you use this without performing thorough testing yourself.

I hope this helps someone.

		DROP TABLE ADMIN_PUBLIC_HOLIDAYS;
		drop function calculate_easter_sunday;
		DROP PROCEDURE GENERATE_PUBLIC_HOLIDAYS;


		CREATE TABLE ADMIN_PUBLIC_HOLIDAYS (
		  HOLIDAY_DATE                DATE,
		  HOLIDAY_NAME                VARCHAR2(50) )
		TABLESPACE xxx_huge
		LOGGING
		STORAGE ( INITIAL 1M NEXT 1M )
		;
		/
		CREATE UNIQUE INDEX ADMIN_PUBLIC_HOLIDAYS_IDX1 ON 
		  ADMIN_PUBLIC_HOLIDAYS (HOLIDAY_DATE) 
		  TABLESPACE xxx_BIG_IX 
		  STORAGE(INITIAL 500k NEXT 250k ) 
		; 

		/

		CREATE OR REPLACE
		FUNCTION calculate_easter_sunday(
			  pn_year NUMBER) return date
		  IS
		/***************************************************
		  Name:         calculate_easter_sunday
		  Created Date: 01/5/2012
		  Author:       Vivian Farrell
		  Input:        Year
		  Output:       Date of easter sunday

		  Description:  This function has been ported from the
		  BASIC code available from http://www.assa.org.au/edm.html.
		  The dates that are generated are used by the Australian
		  Government to calculate the easter period each year.
		****************************************************/
			ln_day       NUMBER;
			ln_month     NUMBER;
			ln_first_dig NUMBER;
			ln_remain_19 NUMBER;
			ln_temp      NUMBER;
			ln_tA        NUMBER;
			ln_tB        NUMBER;
			ln_tC        NUMBER;
			ln_tD        NUMBER;
			ln_tE        NUMBER;
			lv_tmp      varchar(50);
		  BEGIN
			ln_first_dig := floor(pn_year / 100);    -- first 2 digits of year
			ln_remain_19 := mod (pn_year,19); --remainder of year / 19
			-- calculate PFM date
			ln_temp := floor((ln_first_dig - 15) / 2) + 202 - 11 * ln_remain_19;
			 
			if ( ln_first_dig in (21, 24, 25, 27,28,29,30,31,32, 34, 35, 38) )THEN 
			  ln_temp := ln_temp - 1;
			elsif( ln_first_dig IN (33, 36, 37, 39, 40) ) THEN
			  ln_temp    := ln_temp - 2;
			end if;
			
			ln_temp := Mod(ln_temp, 30);
			ln_tA       := ln_temp + 21;
			IF (ln_temp  = 29) THEN
			  ln_tA     := ln_tA - 1;
			END IF;
			IF (ln_temp = 28 AND ln_remain_19 > 10) THEN
			  ln_tA    := ln_tA - 1;
			END IF;
			--find the next Sunday
			ln_tB   := Mod((ln_tA - 19),7);
			ln_tC   := Mod((40    - ln_first_dig), 4);
			IF ln_tC = 3 THEN
			  ln_tC := ln_tC + 1;
			END IF;
			IF ln_tC > 1 THEN
			  ln_tC := ln_tC + 1;
			END IF;
			ln_temp := Mod(pn_year, 100);
			ln_tD   := Mod (floor(ln_temp + ln_temp / 4),7);
			ln_tE   := Mod((20       - ln_tB - ln_tC - ln_tD), 7) + 1;
			ln_day  := ln_tA         + ln_tE;
			--return the date
			IF ln_day       > 31 THEN
			  ln_day       := ln_day - 31;
			  ln_month     := 4;
			ELSE
			  ln_month := 3;
			END IF;
			lv_tmp := concat(concat(lpad(ln_day,2,'0'),lpad(ln_month,2,'0')),pn_year);
			RETURN to_date(lv_tmp,'DDMMYYYY');
		  END;

		/
		create or replace
		PROCEDURE Generate_Public_Holidays(start_year number, end_year number)
		IS
		/***************************************************
		  Name:         Generate_Public_Holidays
		  Created Date: 01/5/2012
		  Author:       Vivian Farrell
		  Input:        start year, end year
		  Output:       ADMIN_PUBLIC_HOLIDAYS updated
		  Dependencies: calculate_easter_sunday function

		  Description:  This procedure calculates all Victorian
		  public holidays according to the Public Holidays Act
		  1993 and populates them into the ADMIN_PUBLIC_HOLIDAYS
		  table.
		****************************************************/
		  tempday VARCHAR2(50);
		  nydate DATE;
		  ausdate DATE;
		  labdate DATE;
		  easter_friday DATE;
		  easter_monday date;
		  anzacdate DATE;
		  queendate DATE;
		  cupdate date;
		  chrisdate DATE;
		  boxingdate date;
		  
		BEGIN

		  FOR i IN start_year..end_year loop
			---- New Years Day - 1st Jan
			tempday := to_char(to_Date(concat('01-JAN-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY') THEN
			  nydate := to_date(concat('03-JAN-',i),'DD-MON-YYYY');
			ELSIF (trim(tempday) = 'SUNDAY') THEN
			  nydate := to_date(concat('02-JAN-',i),'DD-MON-YYYY');
			ELSE
			  nydate := to_date(concat('01-JAN-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (nydate,'NEW YEARS DAY');

			-- Australia Day - 26th Jan
			tempday := to_char(to_Date(concat('26-JAN-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY') THEN
			  ausdate := to_date(concat('28-JAN-',i),'DD-MON-YYYY');
			ELSIF (trim(tempday) = 'SUNDAY') THEN
			  ausdate := to_date(concat('27-JAN-',i),'DD-MON-YYYY');
			ELSE
			  ausdate := to_date(concat('26-JAN-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (ausdate,'AUSTRALIA DAY');

			-- Labour Day - 2nd Monday of March
			labdate := 
				next_day(
				  to_char(
					next_day(
					  to_char(
						last_day(
						  concat('01-FEB-',i)
						)
					  ,'DD/MON/YYYY')
					,'MONDAY')
				  ,'DD/MON/YYYY')
				,'MONDAY');
				
			insert into ADMIN_PUBLIC_HOLIDAYS values (labdate,'LABOUR DAY');
			
			-- Easter Friday
			easter_friday := calculate_easter_sunday(i) - 2;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (easter_friday,'EASTER FRIDAY');

			-- Easter Monday
			easter_monday := calculate_easter_sunday(i) + 1;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (easter_monday,'EASTER MONDAY');
			
			-- ANZAC day - 25th April
			anzacdate := to_date(concat('25-APR-',i),'DD-MON-YYYY');
			-- if anzac day falls on easter monday
			IF (to_char(easter_monday,'DD-MM-YYYY') = to_char(anzacdate, 'DD-MM-YYYY')) THEN
			  anzacdate := anzacdate + 1;
			END IF;
			INSERT INTO ADMIN_PUBLIC_HOLIDAYS VALUES (anzacdate,'ANZAC DAY');
			
			-- Queens Birthday - 2nd Monday in June
			queendate := 
				next_day(
				  to_char(
					next_day(
					  to_char(
						last_day(
						  concat('31-MAY-',i)
						)
					  ,'DD/MON/YYYY')
					,'MONDAY')
				  ,'DD/MON/YYYY')
				,'MONDAY');
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (queendate,'QUEENS BIRTHDAY');
			
			-- Melbourne Cup day - 1st Tuesday in November
			cupdate := 
					next_day(
					  to_char(
						last_day(
						  concat('31-OCT-',i)
						)
					  ,'DD/MON/YYYY')
					,'TUESDAY');
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (cupdate,'MELBOURNE CUP DAY');
			
			-- Christmas day - 25th or 27th if on weekend
			tempday := to_char(to_Date(concat('25-DEC-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY' OR trim(tempday) = 'SUNDAY') THEN
			  chrisdate := to_date(concat('27-DEC-',i),'DD-MON-YYYY');
			ELSE
			  chrisdate := to_date(concat('25-DEC-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (chrisdate,'CHRISTMAS DAY');
			
			-- Boxing day - 26th or 28th if on weekend
			tempday := to_char(to_Date(concat('26-DEC-',i),'DD-MON-YYYY'),'DAY');
			IF (trim(tempday) = 'SATURDAY' OR trim(tempday) = 'SUNDAY') THEN
			  boxingdate := to_date(concat('28-DEC-',i),'DD-MON-YYYY');
			ELSE
			  boxingdate := to_date(concat('26-DEC-',i),'DD-MON-YYYY');
			END IF;
			
			insert into ADMIN_PUBLIC_HOLIDAYS values (boxingdate,'BOXING DAY');
			
		  
		  END loop;
		END;

		/

		DECLARE
		  START_YEAR NUMBER;
		  END_YEAR NUMBER;
		BEGIN
		  START_YEAR := 1993; -- start when the Act was brought in
		  END_YEAR := 2100; -- should be enough for now.

		  GENERATE_PUBLIC_HOLIDAYS(
			START_YEAR => START_YEAR,
			END_YEAR => END_YEAR
		  );
		END;

