Greetings -

Using InterBase 6.0.1.6

I am trying to compare two dates in a stored procedure but cannot get it to work properly. Here is the stored procedure:

DECLARE VARIABLE iMaxFeeRev SMALLINT;
DECLARE VARIABLE iFeeRevNo SMALLINT;
DECLARE VARIABLE dAgencyPercent NUMERIC(3,2);
DECLARE VARIABLE dPercent NUMERIC(3,2);
DECLARE VARIABLE EffectiveThruDate DATE;
DECLARE VARIABLE CurrentDate DATE;
DECLARE VARIABLE iCollPlanID SmallInt;
BEGIN
iMaxFeeRev = -1;
dAgencyPercent = 0;
CurrentDate = CURRENT_DATE;

// Loop through the Collection plan table to insert a new fee into the coll_plan_detail
FOR SELECT CP.COLL_PLAN_ID
FROM COLL_PLAN CP
WHERE CP.PMT_SEQ_ID = :v_PMT_SEQ_ID
INTO :iCollPlanID
DO
BEGIN
// Loop through the fee_code_rev table to get the current fee agency_percent
FOR SELECT FCR.FEE_CODE_REV_NO, FCR.AGENCY_PERCENT, FCR.EFFECTIVE_THRU_DATE
FROM FEE_CODE_REV FCR
WHERE FCR.FEE_ID = :V_FEE_ID
ORDER BY FCR.FEE_CODE_REV_NO DESC
INTO :iFeeRevNo, :dPercent, :EffectiveThruDate
DO
BEGIN
// If the revision is still effective then set the agency percent
IF (EffectiveThruDate > CurrentDate) THEN
BEGIN
iMaxFeeRev = iFeeRevNo;
dAgencyPercent = dPercent;
END
END

// Insert the fee into the collection plan detail table
INSERT INTO COLL_PLAN_DETAIL (COLL_PLAN_ID, FEE_ID, CREATE_USER, AGENCY_PERCENT)
VALUES (:iCollPlanID, :v_FEE_ID, :v_CREATE_USER, :dAgencyPercent);
END
END


But comparing (EffectiveThruDate > CurrentDate) does not work (no error just doesn't execute the code). For example if EffectiveThruDate = 08/13/2003 and CurrentDate = 07/17/2003 the code inside the if statement is never executed. EFFECTIVE_THRU_DATE is a DATE field in the FEE_CODE_REV table

How should I be comparing these date fields?

Thanks for any help,
Jeff


[Non-text portions of this message have been removed]