Date or Timestamp in where clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Date or Timestamp in where clause

  1. #1
    Join Date
    May 2003
    Posts
    46

    Date or Timestamp in where clause

    I have to run this in a script thro' cron everyday to capture information from 6.00 AM to previous day 6.00 AM.(9i/10g)

    Anyone having any idea what is the right way of using date or timestamp here(where clause)?

    Select
    USERNAME DBA_ID
    ,TERMINAL
    ,to_char(timestamp,'mm/dd/yy hh24:mi:ss') TIMESTAMP
    ,OBJ_NAME
    ,ACTION_NAME
    from
    sys.dba_audit_trail
    where
    to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') between to_date('06:00:00','HH24:MI:SS') and to_date('06:00:00','HH24:MI:SS')-1;

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    date_val between trunc(sysdate-1)+0.25 and trunc(sysdate)+0.25

  3. #3
    Join Date
    May 2003
    Posts
    46
    Quote Originally Posted by mike9
    date_val between trunc(sysdate-1)+0.25 and trunc(sysdate)+0.25

    Thanks Mike...that really helps.Appreciate your quick response

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    I thnik you want this for the prev day:
    SQL> select trunc(sysdate-1)+0.25+1/86400 from dual;

    TRUNC(SYSDATE-1)+0.
    -------------------
    01/28/2007 06:00:01

    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