Date Range Arithmetic

[mozart]

I am looking for some SQL assistance in retrieving records based on date arithmetic.

I have a date type column called 'ENTERDATE', and at a simple level I require to return all records that have a date that is between now eg 2-Jan-2007 and the previous 1-July. eg 01-Jul-2006.

If I were to run this query say Dec 2007, then the range would be
01-Jul-2007 and say 25-Dec-2007.

I know I can use parameters and just type in a to_date..... however i am looking for a calculation which would not require editing of the date range in the query.

Thanks

[tamilselvan]

I think add_months will do the job.
See below:

SQL> select * from t1 order by rdate ;

RDATE               ID
----------- ----------
05-JUL-2006          5
03-OCT-2006          3
23-OCT-2006          2
22-NOV-2006          1
20-JUL-2007          4

SQL> var d1 varchar2(30);
SQL> exec :d1 := sysdate ;

PL/SQL procedure successfully completed.

SQL>  select t1.*, :d1 from t1
  2  where rdate between add_months(:d1, -6) and :d1 ;

RDATE               ID :D1
----------- ---------- --------------------------------
22-NOV-2006          1 01-JAN-2007
23-OCT-2006          2 01-JAN-2007
03-OCT-2006          3 01-JAN-2007
05-JUL-2006          5 01-JAN-2007

SQL> exec :d1 := sysdate+350 ;

PL/SQL procedure successfully completed.

SQL> select t1.*, :d1 from t1
  2  where rdate between add_months(:d1, -6) and :d1 ;

RDATE               ID :D1
----------- ---------- --------------------------------
20-JUL-2007          4 17-DEC-2007

Tamil