Hi Sindu,

>At 08:24 01.05.2001 +0700, you wrote:
>Hi all,
>
>Is there a UDF to find out the last day of a month?
>
>And also to increment/decrement a certain date by any months (such as
>IncMonth(date, NoOfMonth) )?
>
>TIA,
>Sindu

try the following StoredProcs. Just take the SQL script
below and execute it.
You need a IB 6 Dialect 3 database for it.

HTH,
Markus



/*===========================================================================*/
/*Stored Procedure Definitions */
/*===========================================================================*/
commit work;
set autoddl off;
set term ^;

create procedure proc_dayofweek (ADATE date)
returns (RESULT integer)
as
begin
exit;
end
^


create procedure proc_daysofmonth (AYEAR integer, AMONTH integer)
returns (RESULT integer)
as
begin
exit;
end
^


create procedure proc_decodedate (ADATE date)
returns (AYEAR integer, AMONTH integer,
ADAY integer)
as
begin
exit;
end
^


create procedure proc_elapseddays (DATE1 date, DATE2 date)
returns (RESULT integer)
as
begin
exit;
end
^


create procedure proc_encodedate (AYEAR integer, AMONTH integer, ADAY integer)
returns (RESULT )
as
begin
exit;
end
^


create procedure proc_incdate (IN_DATE date, IN_SECONDS integer, IN_MINUTES
integer,
IN_HOURS integer, IN_DAYS integer, IN_MONTHS integer, IN_YEARS integer)
returns (OUT_RESULT date)
as
begin
exit;
end
^


create procedure proc_modulus (DIVIDEND integer, DIVISOR integer)
returns (RESULT integer)
as
begin
exit;
end
^


commit work^
set autoddl on^
set term ;^


/*===========================================================================*/
/*Stored Procedure Definitions */
/*===========================================================================*/
commit work;
set autoddl off;
set term ^;

alter procedure proc_dayofweek (ADATE date)
returns (RESULT integer)
as
DECLARE VARIABLE Temp_DOW INTEGER;
BEGIN

Temp_DOW=Extract (WeekDay from ADate);

/*
Used to get the same Numbers as Delphi's DayOfWeek() function
Result:
1 - Sunday; 2 - Monday; 3 - Tuesday; 4 - Wdnesday; 5 - Thursday; 6 -
Friday; 7 - Saturday;
*/

If (Temp_DOW=1) then Result=2;
If (Temp_DOW=2) then Result=3;
If (Temp_DOW=3) then Result=4;
If (Temp_DOW=4) then Result=5;
If (Temp_DOW=5) then Result=6;
If (Temp_DOW=6) then Result=7;
If (Temp_DOW=7) then Result=1;
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
^


alter procedure proc_decodedate (ADATE date)
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_elapseddays (DATE1 date, DATE2 date)
returns (RESULT integer)
as
BEGIN
Result = Date2-Date1;
Suspend;
END
^


alter procedure proc_encodedate (AYEAR integer, AMONTH integer, ADAY integer)
returns (RESULT )
as
BEGIN
Result = cast( ADay || '.' || AMonth || '.' || AYear as DATE);
suspend;
END
^


alter procedure proc_incdate (IN_DATE date, IN_SECONDS integer, IN_MINUTES
integer,
IN_HOURS integer, IN_DAYS integer, IN_MONTHS integer, IN_YEARS integer)
returns (OUT_RESULT date)
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;

/* taken from RxLib, unit DateUtils.pas */
/* 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;


/* taken from RxLib, unit DateUtils.pas */
/*
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 number */

IF(Dividend = 0) THEN Result = 0;
ELSE
Result = Dividend-(CAST((Dividend / Divisor)-0.49 AS INTEGER)*Divisor);
Suspend;
END
^

commit work^
set autoddl on^
set term ;^

/*===========================================================================*/