Subject Re: [ib-support] Re: [IBO] Date Question - date procs Markus Ostenried 2001-08-10T10:48:20Z
Helen

> Would you like to donate this to the ReallyUseful pages on the IBDI site?

of course. You can copy the procs from my previous message.
Below is my SP for eastersunday, too. You just have to replace the
ALTER with CREATE since I just copied the SPs from IB_WISQL.

Regards,
Markus

ALTER PROCEDURE PROC_EASTERSUNDAY( IN_YEAR INTEGER )
RETURNS ( OUT_DATE DATE )
AS
DECLARE VARIABLE var_Month INTEGER;
DECLARE VARIABLE var_Day INTEGER;
DECLARE VARIABLE var_Moon INTEGER;
DECLARE VARIABLE var_Epact INTEGER;
DECLARE VARIABLE var_Sunday INTEGER;
DECLARE VARIABLE var_Gold INTEGER;
DECLARE VARIABLE var_Cent INTEGER;
DECLARE VARIABLE var_Corx INTEGER;
DECLARE VARIABLE var_Corz INTEGER;
DECLARE VARIABLE var_Tmp INTEGER;
BEGIN
/*{ The Golden Number of the year in the 19 year Metonic Cycle: }*/
EXECUTE PROCEDURE PROC_MODULUS( :in_Year, 19 ) RETURNING_VALUES (
:var_Gold );
var_Gold = var_Gold + 1;
/*{ Calculate the Century: }*/
var_Cent = CAST(in_Year/100-0.49 AS INTEGER) + 1;
/*{ Number of years in which leap year was dropped in order... }*/
/*{ to keep in step with the sun: }*/
var_Corx = (3 * var_Cent);
var_Corx = CAST(var_Corx/4-0.49 AS INTEGER) - 12;
/*{ Special correction to syncronize Easter with moon's orbit: }*/
var_Corz = (8 * var_Cent + 5);
var_Corz = CAST(var_Corz/25-0.49 AS INTEGER) - 5;
/*{ Find Sunday: }*/
var_Sunday = (5 * in_Year);
var_Sunday = CAST(var_Sunday/4-0.49 AS INTEGER) - var_Corx - 10;
/*{ Set Epact - specifies occurrence of full moon: }*/
var_Epact = (11 * var_Gold + 20 + var_Corz - var_Corx);
EXECUTE PROCEDURE PROC_MODULUS( :var_Epact, 30 ) RETURNING_VALUES (
:var_Epact );
if (var_Epact < 0) then begin
var_Epact = var_Epact + 30;
end
if (((var_Epact = 25) and (var_Gold > 11)) or (var_Epact = 24)) then begin
var_Epact = var_Epact + 1;
end
/*{ Find Full Moon: }*/
var_Moon = 44 - var_Epact;
if (var_Moon < 21) then begin
var_Moon = var_Moon + 30;
end
var_Tmp = var_Sunday + var_Moon;
EXECUTE PROCEDURE PROC_MODULUS( :var_Tmp, 7 ) RETURNING_VALUES ( :var_Tmp );
var_Moon = var_Moon + 7 - var_Tmp;
if (var_Moon > 31) then begin
var_Month = 4;
var_Day = var_Moon - 31;
end else begin
var_Month = 3;
var_Day = var_Moon;
end

EXECUTE PROCEDURE PROC_EncodeDate( :in_Year, :var_Month, :var_Day )
RETURNING_VALUES ( :out_DATE );
Suspend;

/*(*
// Jedi Code Library: JclDateTime.pas:
//
// Calculates and returns Easter Day for specified year.
// Originally from Mark Lussier, AppVision <MLussier@...>.
// Corrected to prevent integer overflow if it is inadvertedly
// passed a year of 6554 or greater.

function EasterSunday(const Year: Integer): TDateTime;
var
Month, Day, Moon, Epact, Sunday,
Gold, Cent, Corx, Corz: Integer;
begin
{ The Golden Number of the year in the 19 year Metonic Cycle: }
Gold := (Year mod 19) + 1;
{ Calculate the Century: }
Cent := (Year div 100) + 1;
{ Number of years in which leap year was dropped in order... }
{ to keep in step with the sun: }
Corx := (3 * Cent) div 4 - 12;
{ Special correction to syncronize Easter with moon's orbit: }
Corz := (8 * Cent + 5) div 25 - 5;
{ Find Sunday: }
Sunday := (Longint(5) * Year) div 4 - Corx - 10;
{ ^ To prevent overflow at year 6554}
{ Set Epact - specifies occurrence of full moon: }
Epact := (11 * Gold + 20 + Corz - Corx) mod 30;
if Epact < 0 then
Epact := Epact + 30;
if ((Epact = 25) and (Gold > 11)) or (Epact = 24) then
Epact := Epact + 1;
{ Find Full Moon: }
Moon := 44 - Epact;
if Moon < 21 then
Moon := Moon + 30;
Moon := Moon + 7 - ((Sunday + Moon) mod 7);
if Moon > 31 then
begin
Month := 4;
Day := Moon - 31;
end
else
begin
Month := 3;
Day := Moon;
end;
Result := EncodeDate(Year, Month, Day);
end;
*)*/

END

ALTER PROCEDURE PROC_CALCFEIERTAGE( IN_START DATE
, IN_STOP DATE )
RETURNS ( OUT_START TIMESTAMP
, OUT_STOP TIMESTAMP
, OUT_NAME VARCHAR(50) )
AS
DECLARE VARIABLE var_Year INTEGER;
DECLARE VARIABLE var_StopYear INTEGER;
DECLARE VARIABLE var_Ostern DATE;
BEGIN
/* returns german "Feiertage" which are in range of the input params */
/* author: Markus Ostenried, chef_007@..., markus@... */

var_Year = EXTRACT( YEAR FROM in_Start );
var_StopYear = EXTRACT( YEAR FROM in_Stop );
WHILE (var_Year <= var_StopYear) DO BEGIN

/* Neujahr */
out_Start = CAST('01.01.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Neujahr';
Suspend;
END

/* Heilige drei Koenige */
out_Start = CAST('06.01.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Heilige drei Koenige';
Suspend;
END

/* Maifeiertag */
out_Start = CAST('01.05.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Maifeiertag';
Suspend;
END

/* Tag der dt. Einheit */
out_Start = CAST('03.10.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Tag der dt. Einheit';
Suspend;
END

/* 1. Weihnachtsfeiertag */
out_Start = CAST('25.12.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = '1. Weihnachtsfeiertag';
Suspend;
END

/* 2. Weihnachtsfeiertag */
out_Start = CAST('26.12.' || var_Year || ' 00:00:00' AS TIMESTAMP);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = '2. Weihnachtsfeiertag';
Suspend;
END

EXECUTE PROCEDURE PROC_EASTERSUNDAY( var_Year ) RETURNING_VALUES (
var_Ostern );

/* Ostersonntag */
out_Start = CAST(var_Ostern AS DATE) + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Ostersonntag';
Suspend;
END

/* Ostermontag */
out_Start = CAST(var_Ostern AS DATE) + 1 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Ostermontag';
Suspend;
END

/* Aschermittwoch */
out_Start = CAST(var_Ostern AS DATE) - 46 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Aschermittwoch';
Suspend;
END

/* Karfreitag */
out_Start = CAST(var_Ostern AS DATE) - 2 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Karfreitag';
Suspend;
END

/* Christi Himmelfahrt */
out_Start = CAST(var_Ostern AS DATE) + 39 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Christi Himmelfahrt';
Suspend;
END

/* Pfingstsonntag */
out_Start = CAST(var_Ostern AS DATE) + 49 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Pfingstsonntag';
Suspend;
END

/* Pfingstmontag */
out_Start = CAST(var_Ostern AS DATE) + 50 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Pfingstmontag';
Suspend;
END

/* Fronleichnam */
out_Start = CAST(var_Ostern AS DATE) + 60 + CAST('00:00:00' AS TIME);
out_Stop = CAST(out_Start AS DATE) + CAST('23:59:59' AS TIME);
IF (NOT (out_STOP < in_START) AND NOT (out_START > in_STOP)) THEN BEGIN
OUT_NAME = 'Fronleichnam';
Suspend;
END

var_Year = var_Year + 1;
END

/*
http://www.weltzeituhr.com/infos/kirchliche_feiertage.htm
Aschermittwoch : 46 Tage vor Ostersonntag
Karfreitag : 2 Tage vor Ostersonntag
Christi Himmelfahrt : 39 Tage nach Ostersonntag
Pfingstsonntag : 49 Tage nach Ostersonntag
Fronleichnam : 60 Tage nach Ostersonntag
*/
END