Subject | Re: Number of months between dates |
---|---|
Author | Raigo |
Post date | 2004-05-12T14:35:18Z |
Thanks for everybody. I got it to work :)
procedure is now following:
CREATE PROCEDURE SP_PUHKUS (
BEGIN_DATE TIMESTAMP,
END_DATE TIMESTAMP
) RETURNS (
WORKED_MONTHS INTEGER
) AS
declare variable ADay Integer;
declare variable AMonth Integer;
declare variable AYear Integer;
declare variable LDay Integer;
declare variable LMonth Integer;
declare variable LYear Integer;
declare variable years Integer;
declare variable months Integer;
BEGIN
ADay = EXTRACT(DAY from begin_date);
AMonth = EXTRACT(MONTH from begin_date);
AYear = EXTRACT(YEAR from begin_date);
LDay = EXTRACT(DAY from end_date);
LMonth = EXTRACT(MONTH from end_date);
LYear = EXTRACT(YEAR from end_date);
years=LYear-AYear;
months=LMonth-AMonth;
worked_months=(years*12)+months;
If (LDay<15) then worked_months=worked_months-1;
If (ADay<=15) then worked_months=worked_months+1;
If ((ADay<=15) and (LDay<15) and (AMonth<>LMonth) and (AYear<>LYear)) then worked_months=worked_months+1;
SUSPEND;
END
procedure is now following:
CREATE PROCEDURE SP_PUHKUS (
BEGIN_DATE TIMESTAMP,
END_DATE TIMESTAMP
) RETURNS (
WORKED_MONTHS INTEGER
) AS
declare variable ADay Integer;
declare variable AMonth Integer;
declare variable AYear Integer;
declare variable LDay Integer;
declare variable LMonth Integer;
declare variable LYear Integer;
declare variable years Integer;
declare variable months Integer;
BEGIN
ADay = EXTRACT(DAY from begin_date);
AMonth = EXTRACT(MONTH from begin_date);
AYear = EXTRACT(YEAR from begin_date);
LDay = EXTRACT(DAY from end_date);
LMonth = EXTRACT(MONTH from end_date);
LYear = EXTRACT(YEAR from end_date);
years=LYear-AYear;
months=LMonth-AMonth;
worked_months=(years*12)+months;
If (LDay<15) then worked_months=worked_months-1;
If (ADay<=15) then worked_months=worked_months+1;
If ((ADay<=15) and (LDay<15) and (AMonth<>LMonth) and (AYear<>LYear)) then worked_months=worked_months+1;
SUSPEND;
END