I want to query result with specific year ranges-CodePudding

I want to query a result with specific year ranges. For example result with the year 5 to 10 .

select * from Employee
where dateHired >= 5 and dateHired <= 10

CodePudding user response：

There are many ways to do this, but one of the simplest is this:

SELECT *
FROM employees e
WHERE MONTHS_BETWEEN(e.dateHired, SYSDATE) BETWEEN 60 AND 120;

CodePudding user response：

To find dates e.g. between 2005 and 2010, you may use:

SELECT *
FROM Employee
WHERE dateHired >= date '2005-01-01' AND dateHired < date '2011-01-01';

CodePudding user response：

I like Del's solution, but it will not use the index for the dateHired column, if it exists. I suggest a variant of the solution, which will do INDEX RANGE SCAN.

SELECT *
FROM employees e
WHERE e.dateHired BETWEEN SYSDATE - interval '1' year * 10 and SYSDATE -interval '1' year * 5 ;