# 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
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`.