Date range in sql

I want to write a query where it displays data between 01-April-2010 to lastday-april-2011.

But I don't want to hard code the year. I开发者_如何学Pythont should take from system date so it only displays records from this year april to next year april.

Thanks in advance for the help.

select add_months( trunc(sysdate,'YYYY'), -12 ) 
       + interval '4' month 
       + level 
       - 1
  from dual
connect by level <= ( (trunc(sysdate,'YYYY') 
                       + interval '4' month) -
                      (add_months( trunc( sysdate, 'YYYY' ), -12 ) 
                       + interval '4' month) )

should work. You could probably simplify the expression that computes the number of rows but I'd have to think for a few minutes about leap years.

If the date column has no time component:

select ...
from mytable
where datecol between add_months(trunc(sysdate,'YYYY'),3)
                  and add_months(trunc(sysdate,'YYYY'),16)-1

If the date column has a time component:

select ...
from mytable
where datecol >= add_months(trunc(sysdate,'YYYY'),3)
and   datecol < add_months(trunc(sysdate,'YYYY'),16)

Your query should be something like this

SELECT * FROM youtable WHERE datecolumn BETWEEN to_char(sysdate,'yyyy')+'0401' AND to_char(sysdate,'yyyy')+'0430'