Year and date functions

[newbby]

Hi,
Can you please tell me how do I write this logic in PL/SQL. I have 2 date fields: date1 and sysdate. I need to pull all records from tableA where the difference between the years from the 2 dates should be <= 3
In sql you would write as
select * from tableA
where datediff(yy,date1,getdate()) <= 3.
Thanks

 

[lewisp]

WHERE To_Number(To_Char(SYSDATE,'YYYY')) -
      To_Number(To_Char(date1,'YYYY')) <= 3

This is assuming that you mean the year parts of the date are <= 3 rather than the dates being <= 3 years apart.

eg. 31-DEC-2003 - 01-JAN-2000 gives a result like you describe, even though the actual dates are almost 4 years apart.

If you want the alternative, try

WHERE date1 >= Add_Months(SYSDATE,-36)