CODEWITHSUNDEEP
sqldateselectsysdate
BigRedEO
BigRedEO

Reputation: 837

SELECT FROM TABLE by YEAR dynamically

I have a query (in Oracle SQL Developer) which is currently hard-coded to select all records from 2019, 2018, and 2017. Now that it's 2020, I'd like to change this to select going back three years dynamically, so that this query can work years from now without having to change the code.

I know that I can find all records from this specific date last year through the end of the year with - 

SELECT * FROM TABLE
WHERE BOOKDATE >= add_months( sysdate, -12*1 );

So that would give me from 11-MAR-2019 through the end of 2019. But how do I select all records for the current year, last year, and two years ago (each year separately) - and not from this date specifically? BOOKDATE has the format DD-MON-YY.

Upvotes: 1

Views: 815

Answers (2)

Mureinik
Mureinik

Reputation: 311393

You could extract the year and perform the calculation on it:

SELECT *
FROM   mytable
WHERE  EXTRACT(YEAR FROM bookdate) - EXTRACT(YEAR FROM SYSDATE) <= 3

Upvotes: 1

dargin
dargin

Reputation: 61

You could try like this: 

SELECT * 
    FROM TABLE 
    WHERE EXTRACT(YEAR from BOOKDATE) >= EXTRACT(YEAR from sysdate)-3

Upvotes: 0

Related Questions