0

I need to select employees whose work experience is currently more or equal to 15 years. Also I have to display their work experience in years in a separate column.

I have tried this

SELECT CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln" 
 FROM HR.EMPLOYEES WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), HIRE_DATE)), '%Y') 
 + 0 >= 15;

I'm stuck on "ORA-00904: "DATE_FORMAT": invalid identifier" and also I don't know how to create a separate column with their work experience (in years).

Database:

DB

tripleee
  • 175,061
  • 34
  • 275
  • 318
Octopus
  • 3
  • 2

1 Answers1

0

A simple option is

where extract(year from sysdate) - extract(year from hire_date) >= 15

If table isn't too large, performance won't suffer because of possible index on hire_date column. If it does, say so.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This is wrong as `EXTRACT(YEAR FROM DATE '2022-11-04') - EXTRACT(YEAR FROM DATE '2007-12-31')` will return 15 but there is less that 15 years between the two dates. – MT0 Nov 04 '22 at 13:46
  • If you're interested in years only, then it is OK, @MT0. If you're interested in months and days as well, then it is wrong. It depends. – Littlefoot Nov 04 '22 at 13:49