[add_months] Are you really 18 years old today?

Today I read Laurent Schneider Blog. I like that.

He ask "Are you really 18 years old today?".

If you were born Feb 28, 1990. You want to find date in next 18 years for the party.....haha

How can you?

you used "add_months"?

SQL> select add_months(to_date('1990-02-28','YYYY-MM-DD'),16*12) from dual;
ADD_MONTH
---------
28-FEB-06

SQL> select add_months(to_date('1990-02-28','YYYY-MM-DD'),17*12) from dual;
ADD_MONTH
---------
28-FEB-07

SQL> select add_months(to_date('1990-02-28','YYYY-MM-DD'),18*12) from dual;
ADD_MONTH
---------
29-FEB-08

Oh No! I need to wait one day for my birthday.....

OK... OK I should create new func "add_mon"

-------

create function add_mon (date1 IN DATE,add_mon IN NUMBER) return DATE IS loc_date2 date;

BEGIN
loc_date2:= ADD_MONTHS(date1-1,add_mon);

IF LAST_DAY(date1) = date1
THEN
IF LAST_DAY(loc_date2)=loc_date2
THEN
RETURN loc_date2;
ELSE
RETURN loc_date2 +1;
END IF;
ELSE
RETURN ADD_MONTHS(date1,add_mon);
END IF;

END;
/

SQL> l
1 declare
2 a date;
3 begin
4 a := add_mon (to_date('1990-02-27','YYYY-MM-DD'),18*12);
5 dbms_output.put_line(a);
6* end;
SQL> /
27-FEB-08

SQL> l
1 declare
2 a date;
3 begin
4 a := add_mon (to_date('1990-02-28','YYYY-MM-DD'),18*12);
5 dbms_output.put_line(a);
6* end;
SQL> /
28-FEB-08