Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: give specific date and time

    hi, i have a very huge table that contains a date in the format '7/7/03 9:06:17 PM'. according to the data model, this is the date the record was last modified. What I need to do is to write a query that returns the entire records for those whose date column is between today's 7:00:00 AM and yesterday's 7:00:00 AM. This query shud be run everyday. Basically recording whatever records have changed between yesterday 7am and today 7am, no matter when the query is run. i know how to just compare dates but this involves times too.. i tried many ways, cant get it to work. can anyone please help?

    thanks in advance
    -chinna

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    trunc((sysdate)-1) + 420/1440 ... Yesterday at 7:00am
    trunc(sysdate) + 420/1440 ... Today at 7:00am

    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    9

    Thanks a lot..

    Thanks a lot Gregg..

    -Chinna


    Originally posted by gbrabham
    trunc((sysdate)-1) + 420/1440 ... Yesterday at 7:00am
    trunc(sysdate) + 420/1440 ... Today at 7:00am

    Gregg

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    This is a auditing issue. look into FGA...http://download-west.oracle.com/docs...audit.htm#1108
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  5. #5
    Join Date
    Oct 2003
    Location
    INDAI
    Posts
    1

    URGENT

    Hi gregg

    Could you plz tell me what is 420/1440 's relevence i could not get it


    Thanks.
    QUOTE]Originally posted by gbrabham
    trunc((sysdate)-1) + 420/1440 ... Yesterday at 7:00am
    trunc(sysdate) + 420/1440 ... Today at 7:00am

    Gregg
    [/QUOTE]

  6. #6
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Guess this makes it clear...


    Code:
    SQL> select to_char(trunc((sysdate)-1)+7/24 , 'dd/mm/yyyy hh:mi:ss') as yesterda
    y_7AM
      2  from dual
      3  /
    
    YESTERDAY_7AM
    -------------------
    30/09/2003 07:00:00
    Cheers...
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    1440 ARE THE MINUTES IN A DAY ...

    Gregg

Posting Permissions

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