How to calculate and select employees of age above 25 years 2 months from database in Oracle?

Question

We have employees of all ages in the database. We need to select only those employees whose age is more than 25 years 2 months.

Answer 1

this will work:

SELECT * FROM emp where sysdate-birthdate>=(SELECT sysdate- 
to_date('19931015','YYYYMMDD') FROM dual);

Answer 2

you could use the months_between function https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

Oracles own example:

 SELECT MONTHS_BETWEEN     (TO_DATE('02-02-1995','MM-DD-YYYY'),
     TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
     FROM DUAL;

Months

1.03225806

your query would be something like

select * from emp where MONTHS_BETWEEN (sysdate, date_of_birth) > ((25*12)+2)

Answer 3

ADD_MONTHS might help, e.g.

select *
from employees
where date_of_birth > add_months(trunc(sysdate), -(25 * 12 + 2));

• ADD_MONTHS adds number of months to the first parameter
• TRUNC(SYSDATE) returns today's day at midnight; dates of birth usually don't have a time component.
• the second parameter is:
  • -, because we have to subtract months ("25 years ago")
  • 25 - 25 years
  • 12 - a year has 12 months
  • + 2 add 2 months