CODEWITHSUNDEEP
plsqldate-arithmetic
Auwal
Auwal

Reputation: 17

PL SQL Retirement function

I need a help on this function. The code has no error but it keeps returning the same result, that the value from of the second condition/statement.

This is how it suppose to work: If employees age at hire date (DOFA) is less than or equals to 25 the retirement date is 35 years from hire date. Otherwise retirement date is when employees age is 60

create or replace function EDOR_FUNCTION
    (DOFA in date, DOB in date) 
    return date
is
    new_edor_date date;

begin

  if 
      DOFA - DOB <= 25 then new_edor_date := add_months(DOFA,  35*12);
  else
      new_edor_date := add_months(DOB, 60*12);
  end if;

  return  new_edor_date;

end;

Upvotes: 0

Views: 240

Answers (1)

APC
APC

Reputation: 146239

Your condition subtracts one date from another. This gives the number of days between the two, not the number of years.

months_between() gives the number of months between two dates. Multiply by twelve to get number of years 

if  months_between(DOFA , DOB) <= (25*12) then 
     new_edor_date := add_months(DOFA,  35*12);
else
     new_edor_date := add_months(DOB, 60*12);
end if;

Upvotes: 1

Related Questions