DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query - with date in where clause

  1. #1
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43

    Query - with date in where clause

    Hi,t

    I'm trying to run a query to return values from the 10th of last month to the 9th of this month, whenever it is ran. I tried add_months but it is giving me records from a month ago e.g.

    select name,dept,age
    from mytable
    where hire_date >= add_months(sysdate,-1)

    I need the query to return value from 10th of last month to 9th of this month no matter when it is ran.

    Thanks
    OCP 8i DBA
    I'm still learning

  2. #2
    Join Date
    Jan 2002
    Location
    NC, USA
    Posts
    24
    romeo@oradev>select
    2 to_char(add_months(trunc(sysdate,'MONTH'),-1)+9,'MM/DD/YYYY') last_month,
    3 to_char(add_months(trunc(sysdate,'MONTH'),0)+8,'MM/DD/YYYY') this_month
    4 from dual
    5 ;

    LAST_MONTH THIS_MONTH
    ---------- ----------
    05/10/2004 06/09/2004

    romeo@oradev>
    OCP DBA 8, 8i, 9i, 10g

  3. #3
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thx Romeo, I will try this.
    OCP 8i DBA
    I'm still learning

  4. #4
    Join Date
    May 2001
    Location
    London
    Posts
    149
    You should try:

    select last_day(add_months(sysdate,-2))+10,last_day(add_months(sysdate,-1))+9 from dual;

  5. #5
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thx for your suggestions guys. What I'm really trying to do is to allow my query to get values between 10th of last month and 9th of this month whenever the query is ran. It is not just simply select those dates.

    Anyway thanks
    OCP 8i DBA
    I'm still learning

  6. #6
    Join Date
    Jan 2002
    Location
    NC, USA
    Posts
    24
    select name,dept,age
    from mytable
    where hire_date between add_months(trunc(sysdate,'MONTH'),-1)+9 and
    add_months(trunc(sysdate,'MONTH'),0)+8
    ;
    OCP DBA 8, 8i, 9i, 10g

  7. #7
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thanks Romeo, I was able to use to achieve the task by specifying the mon/year at run time

    e.g. @c:\mytest2.sql mar/2004

    with the where clase below:

    between add_months(to_date('16/&1','dd/mon/yyyy'),-1) and
    add_months(to_date('16/&1','dd/mon/yyyy'),+0)-1

    -- &1 will be mar/2004

    Thanks a lot
    OCP 8i DBA
    I'm still learning

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