select record for recent n years

0
votes

How to select the records for the 3 recent years, with JUST considering the year instead of the whole date dd-mm-yy.
let's say this year is 2013, the query should display all records from 2013,2012 and 2011.
if next year is 2014, the records shall be in 2014, 2013, 2012
and so on....

table foo  

ID    DATE
----- ---------
a0001 20-MAY-10
a0002 20-MAY-10
a0003 11-MAY-11
a0004 11-MAY-11
a0005 12-MAY-11
a0006 12-JUN-12
a0007 12-JUN-12
a0008 12-JUN-12
a0009 12-JUN-13
a0010 12-JUN-13
3
sqloracledateselect

3 Answers

2
votes

I think the clearest way is to use the year() function:

select *
from t
where year("date") >= 2011;

However, the use of a function in the where clause often prevents an index from being used. So, if you have a large amount of data and an index on the date column, then something that uses the current date is better. The following takes this approach:

select *
from t
where "date" >= trunc(add_months(sysdate, -3*12), 'yyyy')
1
votes
SELECT ID, DATE FROM FOO 
    WHERE DATE >= TRUNC(ADD_MONTHS(SYSDATE, -3*12), 'YYYY');

Here we are getting last 36 months data by using ADD_MONTHS. We are adding 12*3 i.e. last 3 years data froom SYSDATE

0
votes

That is pretty easy so I suspect that your question is not clear.

SELECT *
FROM foo
WHERE [date] >= '2011-01-01 00:00:00'

Does that work for you?

UPDATE

If you wish to do this dynamically you can use variables.

Below is how you would do that in SQL Server (I do not know the Oracle syntax for this)

DECLARE @startYear int
SET @startYear = YEAR(GETDATE()) - 2

SELECT *
FROM foo
WHERE [date] >= @startYear + '-01-01 00:00:00'