Age In Years Of Youngest Employee-CodePudding

I have a table called employees and I I need to get the age of the youngest employee in years.

For example if I look at the table the youngest employee is "57 years old"

The columns:

EmployeeID, Lastname, Title, Birthdate, Hiredate, City, Country

The code I was trying was this:

SELECT MAX(birthdate)
FROM employees;

With that I can get the date of birth of the youngest employee, but now I need to somehow compare it with the current date that would be using "sysdate" and then change it to numbers so that it shows that he is 57 years old, but I have not succeeded

CodePudding user response：

You can use:

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, MAX(birthdate))/12) AS age
FROM   employees;

Which, for the sample data:

CREATE TABLE employees ( id, birthdate ) AS
-- First employee will be 20 tomorrow
SELECT 1, ADD_MONTHS(TRUNC(SYSDATE), -20*12)   INTERVAL '1' DAY FROM DUAL UNION ALL
-- Second employee is 25 today
SELECT 2, ADD_MONTHS(TRUNC(SYSDATE), -25*12) FROM DUAL;

Outputs:

AGE
19

fiddle

CodePudding user response：

If you don't care much about months and days, a simple option is to extract year from sysdate and youngest birthdate and subtract them:

Sample data:

SQL> with employees (employeeid, lastname, birthdate) as
  2    (select 1, 'Little', date '2015-08-25' from dual union all  --> youngest
  3     select 2, 'Foot'  , date '2000-11-13' from dual
  4    )

Query:

  5  select extract(year from sysdate) - extract(year from max(birthdate)) as age
  6  from employees;

       AGE
----------
         8

SQL>

CodePudding user response：

You can subtract your MAX(birthdate) from SYSDATE - the result is number of days so you should convert days to years.

WITH 
    tbl AS 
        (
            select 
                to_date('09-09-1965', 'dd-mm-yyyy') birthdate,
                SYSDATE my_sysdate
            from   dual
        )
SELECT FLOOR((my_sysdate - MAX(birthdate))/ 365) "YEARS_OLD" From tbl

  YEARS_OLD
-----------
         57