Hi all,

I've translated the following procedures from RxLib to SQL. Works fine for me.
Maybe someone can benefit from them, too.
Or report me some unknown bugs :-)

I have a SP to calculate easter sunday snd the german "feiertage" as SP, too.
If someone is interested... just let me know.

Regards,
Markus


ALTER PROCEDURE PROC_INCDATE( IN_DATE TIMESTAMP
, IN_SECONDS INTEGER
, IN_MINUTES INTEGER
, IN_HOURS INTEGER
, IN_DAYS INTEGER
, IN_MONTHS INTEGER
, IN_YEARS INTEGER )
RETURNS ( OUT_RESULT TIMESTAMP )
AS
DECLARE VARIABLE var_SecondsOfTime INTEGER;
DECLARE VARIABLE var_Time TIME;
DECLARE VARIABLE var_Day INTEGER;
DECLARE VARIABLE var_Month INTEGER;
DECLARE VARIABLE var_Year INTEGER;
DECLARE VARIABLE var_ModHour INTEGER;
DECLARE VARIABLE var_ModMonth INTEGER;
DECLARE VARIABLE var_DaysPerMonth INTEGER;
BEGIN
IF (IN_SECONDS IS NULL) THEN IN_SECONDS = 0;
IF (IN_MINUTES IS NULL) THEN IN_MINUTES = 0;
IF (IN_HOURS IS NULL) THEN IN_HOURS = 0;
IF (IN_DAYS IS NULL) THEN IN_DAYS = 0;
IF (IN_MONTHS IS NULL) THEN IN_MONTHS = 0;
IF (IN_YEARS IS NULL) THEN IN_YEARS = 0;

/* function IncTime(ADateTime: TDateTime; Hours, Minutes, Seconds,
MSecs: Integer): TDateTime; */
/* Result := ADateTime + (Hours div 24) + (((Hours mod 24) * 3600000 +
Minutes * 60000 + Seconds * 1000 + MSecs) / MSecsPerDay); */

var_Time = CAST(in_Date AS TIME);
var_SecondsOfTime = EXTRACT(HOUR FROM var_Time)*60*60 +
EXTRACT(MINUTE FROM var_Time)*60 +
EXTRACT(SECOND FROM var_Time) +
in_Hours*60*60 + in_Minutes*60 + in_Seconds;
in_Days = in_Days + CAST( var_SecondsOfTime/(24*60*60) - 0.49 AS INTEGER );
EXECUTE PROCEDURE PROC_MODULUS( var_SecondsOfTime, 86400 )
RETURNING_VALUES ( var_SecondsOfTime );
var_Time = CAST( '00:00' AS TIME );
var_Time = var_Time + var_SecondsOfTime;



EXECUTE PROCEDURE PROC_DecodeDate( in_Date ) RETURNING_VALUES ( var_Year
,var_Month, var_Day );
var_Year = var_Year + in_Years;
var_Year = var_Year + CAST(in_Months/12-0.49 AS INTEGER);
EXECUTE PROCEDURE PROC_MODULUS( in_Months, 12 ) RETURNING_VALUES (
var_ModMonth );
var_Month = var_Month + var_ModMonth;

IF (var_Month < 1) THEN BEGIN
var_Month = var_Month + 12;
var_Year = var_Year - 1;
END
ELSE
IF (var_Month > 12) THEN BEGIN
var_Month = var_Month - 12;
var_Year = var_Year + 1;
END

EXECUTE PROCEDURE PROC_DAYSOFMONTH( var_Year, var_Month )
RETURNING_VALUES ( var_DaysPerMonth );
IF (var_Day > var_DaysPerMonth) THEN BEGIN
EXECUTE PROCEDURE PROC_DAYSOFMONTH( var_Year, var_Month )
RETURNING_VALUES ( var_Day );
END

EXECUTE PROCEDURE PROC_EncodeDate( var_Year, var_Month, var_Day )
RETURNING_VALUES ( out_Result );
out_Result = out_Result + in_Days;
out_Result = CAST(out_Result AS DATE) + var_Time;

/*
function IncDate(ADate: TDateTime; Days, Months, Years: Integer): TDateTime;
var
D, M, Y: Word;
Day, Month, Year: Longint;
begin
DecodeDate(ADate, Y, M, D);
Year := Y;
Month := M;
Day := D;
Inc(Year, Years);
Inc(Year, Months div 12);
Inc(Month, Months mod 12);
if Month < 1 then begin
Inc(Month, 12);
Dec(Year);
end
else if Month > 12 then begin
Dec(Month, 12);
Inc(Year);
end;
if Day > DaysPerMonth(Year, Month) then
Day := DaysPerMonth(Year, Month);
Result := EncodeDate(Year, Month, Day) + Days + Frac(ADate);
end;
*/
END


ALTER PROCEDURE PROC_MODULUS( DIVIDEND INTEGER
, DIVISOR INTEGER )
RETURNS ( RESULT INTEGER )
AS
BEGIN
/* This procedure calculates the modulus of two numbers */
IF(Dividend = 0) THEN Result = 0;
ELSE
Result = Dividend-(CAST((Dividend / Divisor)-0.49 AS INTEGER)*Divisor);
Suspend;
END


ALTER PROCEDURE PROC_DECODEDATE( ADATE TIMESTAMP )
RETURNS ( AYEAR INTEGER
, AMONTH INTEGER
, ADAY INTEGER )
AS
BEGIN
AYear = Extract (Year from ADate);
AMonth = Extract (Month from ADate);
ADay = Extract (Day from ADate);
Suspend;
END

ALTER PROCEDURE PROC_ENCODEDATE( AYEAR INTEGER
, AMONTH INTEGER
, ADAY INTEGER )
RETURNS ( RESULT DATE )
AS
BEGIN
Result = cast( ADay || '.' || AMonth || '.' || AYear as DATE);
suspend;
END

ALTER PROCEDURE PROC_DAYSOFMONTH( AYEAR INTEGER
, AMONTH INTEGER )
RETURNS ( RESULT INTEGER )
AS
DECLARE VARIABLE WorkDate DATE;
BEGIN
Result = 31;
WHILE (Result > 28 AND WorkDate IS NULL) DO
BEGIN
EXECUTE PROCEDURE Proc_EncodeDate(AYear, AMonth, Result)
RETURNING_VALUES(WorkDate);
WHEN ANY DO Result = Result -1;
END
END


At 11:41 10.08.2001 +0200, you wrote:

>Jason,
>
> > No, is all he needs to do is catch the exception on the case where he is on
> > a leap year day and the next year doesn't have an equivalent month/day
> > combination. In which case he should just subtract one from the day and
> then
> > it will be fine.
>
>that's an approach.
>Another is calculate if the year is a leap year in advance and correct
>the day before encoding the date.
>
>I am used to avoid using exceptions to cater for foreseeable cases,
>because of both style and performance (in Delphi).
>Don't know if the performance hit of WHEN in InterBase SP code is
>significant, perhaps not much.
>Ciao
>--
> ____
>_/\/ando