CODEWITHSUNDEEP
sqloracle-database
aravind
aravind

Reputation: 13

Date difference in oracle

May be this question is dumb one or repeated one but I didnt get exact answe for this

I have table which has date format column(eff_date), I want the data where difference between sysdate anda eff_date is more than two years and equal to two years

I am using this query I know its syntactically wrong 

select * 
from Customer 
where (select floor(months_between(sysdate,eff_date)/12) 
       from Customer t) >= 2

Upvotes: 0

Views: 109

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

If you want to use months_between

SELECT *
  FROM customer
 WHERE months_between( sysdate, eff_date ) >= 24

If there is an index on eff_date, though, you probably want

SELECT *
  FROM customer
 WHERE eff_date <= sysdate - interval '2' year

Upvotes: 1

Related Questions