A page of files or links containing tips, tricks, techniques, practices
and 'gotchas' that Firebird users found 'Really Useful'.
If you have something like this, that you want to share on this page, please
email it to helebor AT iinet DOT net DOT au
with REALLY USEFUL in the Subject header.
|
|
|
Some Stored Procs to Massage Dates
from Markus Ostenried
|
|
I've translated some procedures from RxLib and the Jedi Code Library: JclDateTime.pas to SQL. Works fine for me.
Maybe someone else can benefit from them, too.
Or report me some unknown bugs :-)
The HTML below uses the <pre> & </pre> tags to preformat the text and avoid embedded HTML tags. If you save the page source, you will be able to copy/paste the code sections directly into a fresh text file.
CREATE 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
CREATE 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; Ed.- this isn't recommended */
END
CREATE 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
CREATE PROCEDURE PROC_ENCODEDATE( AYEAR INTEGER
, AMONTH INTEGER
, ADAY INTEGER )
RETURNS ( RESULT DATE )
AS
BEGIN
Result = cast( ADay || '.' || AMonth || '.' || AYear as DATE);
suspend;
END
CREATE 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
Below is an SP for calculating Easter Sunday.
CREATE 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
/*{ Advance to Sunday: }*/
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;
END
This SP calculates the German "Feiertage" (feast days).
CREATE 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@gmx.net, markus@authentic-media.com */
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
|
Back to Really Useful Index
|
|
|
