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

Thread: date query for previous months

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    date query for previous months

    hello

    i am trying to get previous months rows from a table.the table have orderid and oreder created date fields.

    if i run today i need to get all rows with a created date january


    thank you

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    so whats the problem

    where date between 1st jan and 31st jan

  3. #3
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    hello

    sorry i should have been clear .

    yes currently thats what i have in place and trying to aviod hard coded dates

    like "where trunc(order_created_date ) between '01-JAN-2010' and '31-JAN-2010'

    then i have to change every month mnaually and i do not want sysdate -30 which still forces manual entry to change based on the month to i am trying to skip that too

    so my cron runs on feburary 1st and gets all january data and when it runs on march first it gets all february data and so on..

    thanks for your help

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If you do this you can get any month you want, just subtract a different number. You can also create a function based index if you want.

    Code:
    where trunc(order_created_date, 'MM' ) =
          ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)

  5. #5
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thank you ..i see where i went wrong

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