SQL Methods Function to add "Gold Medal" for anniversary I'm trying to create a method function on SQL using Oracle Developer. 

The member function will give employees a gold medal after 12 years of service to the bank, a silver medal after 6 years of service and a bronze medal after 2. 

The issue I am having is with the pre-defined date_type that I have in my employee_type which is as follows

CREATE type employee_Type
AS object(
    name name_type,
    supervisor REF EMPLOYEE_TYPE,
    position VARCHAR2(20),
    salary   NUMBER(5),
    ninum    VARCHAR2(8),
    joindate DATE);


And below is the Method I'm trying to implement

    ALTER type employee_type ADD member FUNCTION give_medals
  RETURN string CASCADE;
CREATE OR REPLACE TYPE BODY employee_type
AS
  MEMBER FUNCTION give_medals
  RETURN STRING
IS
BEGIN
  IF self.join_date &gt; 12 THEN
    RETURN 'Gold Medal';
  ELSIF self.join_date = 6 THEN
    RETURN 'Silver Medal';
  ELSIF self.join_date = 2 THEN
    RETURN 'Bronze Medal!';
  END IF;
END give_medals;
END;


But again my issues is lies with the ELSIF statement, as you can see I'm returning a Gold Medal for anything over 12 - but because the date_type is like 01-JAN-98 everything in the join_date value will be given a gold medal. 

So how could I change the function to give Medals for years of service instead of just anything &gt; 12
First, I assume that the fact that you're using join_date with an underscore in your code while the type uses joindate is a typo.  Otherwise, you'd get a compilation error.

You want to calculate the difference between join_date and the current date.  My guess is that you want to use months_between to calculate the number of months between the two dates and build your logic based on that.  months_between probably does what a human wants when he or she says "12 years" but date arithmetic gets tricky particularly when things like leap days are involved (if you joined on Feb 29, 2016, do you hit your 2 year mark on Feb 28, 2018?  Or March 1, 2018?).  If you need to account for time zones, that makes the logic more difficult.

IF months_between( sysdate, self.join_date ) &gt;= 12*12
THEN
  RETURN 'Gold';
ELSIF months_between( sysdate, self.join_date ) &gt;= 6*12
THEN
  RETURN 'Silver';
ELSIF months_between( sysdate, self.join_date ) &gt;= 2*12
THEN
  RETURN 'Bronze';
ELSE
  RETURN null;
END IF;