Calculate months between two dates For a University course I've been given the following task

Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every employee each year after he/she is hired, must renew  his contract with  company. Show how many months remain before the next renewal for each employee.

This is my attempt:

select (abs(months_between(sysdate,hire_date)/12 - 
         round((months_between(sysdate,e.hire_date))/12)))
from employees e
There are more efficient ways of doing this if your employee table is large, but this is the way I find easiest to understand :)

testbed:

create table employee( employee_id integer primary key, 
                       name varchar(100) not null, 
                       hire_date date not null );

insert into employee(employee_id, name, hire_date)
values(1, 'Alice', to_date('20090909', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(2, 'Bob', to_date('20101010', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(3, 'Chris', to_date('20111111', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(4, 'David', to_date('20101231', 'YYYYMMDD'));


query:

with w as ( select e.*, ( select max(add_months(hire_date, 12*level))
                          from dual
                          connect by add_months(hire_date, 12*(level-1))&lt;sysdate )
                      as next_anniversary
            from employee e )
select w.*, round(months_between(next_anniversary, sysdate)) as months_from_now from w;


result:

EMPLOYEE_ID            NAME       HIRE_DATE                 NEXT_ANNIVERSARY          MONTHS_FROM_NOW
---------------------- ---------- ------------------------- ------------------------- ----------------------
1                      Alice      09-SEP-09 00.00.00        09-SEP-12 00.00.00        10                     
2                      Bob        10-OCT-10 00.00.00        10-OCT-12 00.00.00        11                     
3                      Chris      11-NOV-11 00.00.00        11-NOV-12 00.00.00        12                     
4                      David      31-DEC-10 00.00.00        31-DEC-11 00.00.00        1                      


You may prefer floor or ceil to round.