finding matches fitting between 2 date operations

I want to find all Patients.dob that are between Patient.dob - 20, and patient.dob -30 (in years).

I am u开发者_JAVA技巧sing Oracle 10g, and have tried dateAdd and tried substituting SYSTIME - 12*30 but neither works.

The data is loaded fine and is in the date format as it should be.

How can this be done?

You can extract the year and do a between (but this way only gets the year difference, no necessary the exact 20-30 year difference)

with datam as
(
 select level id, sysdate - level dob
   from dual 
 connect by level < 15000)

 select count(id) count_id , extract(year from dob)
   from datam
  where extract(year from dob) between 2011-30 and 2011-20
  group by extract(year from dob)
  order by 2 desc 

but, ignoring leap years (this is what you were trying to do with the 12*30, it should have been 365*12)

with datam as
(
 select level id, sysdate - level dob
   from dual 
 connect by level < 15000)

 select count(id) count_id , extract(year from dob)
   from datam
  where dob between (sysdate - 365*30) and (sysdate - 365*20)
  group by extract(year from dob)
  order by 2 desc ;

But @Briguy37 works as well:

with datam as
(
 select level id, sysdate - level dob
   from dual 
 connect by level < 15000)

 select count(id) count_id , extract(year from dob)
   from datam
  where dob between add_months(dob,- 12*30) and add_months(sysdate,- 12*20)
  group by extract(year from dob)
  order by 2 desc ;

You can use add_months to do it:

select * from patient p 
  where 
    add_months(p.dob,30 * 12) > current_date and 
    add_months(p.dob,20 * 12) < current_date;