Date Range Arithmetic
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Date Range Arithmetic

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Date Range Arithmetic

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think add_months will do the job.
    See below:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width