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))<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.
Best Answer
Because
to_char()pads the result with spaces (I never understood why).You need to use the "fill mode":