| how to set FROM DATE with time in sql query [message #613172] |
Wed, 30 April 2014 10:25  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi Team,
i am writing select query from DBA_AUDIT_TRAIL to view current date data.
below query works fine and fetches data FROM 30-APR-14 12.00.00 AM till current local time 30-APR-14 11.11.00 AM.
But i would like to filter data only from 30-APR-14 11.00.00 AM to current timestamp.
Could you please help me ?
I tried to issue like below, but getting error:
--below gives error
and A.TIMESTAMP between TO_DATE(to_char('30-APR-14 11.00.00 AM', 'DD-MON-YY HH:MI:SS AM')) and (SYSDATE - interval '4' hour)
select '<|'||D.NAME "DBMS",
I.HOST_NAME "Host Name",
A.OS_USERNAME "OS UserID",
A.USERHOST "User Machine",
to_char(A.TIMESTAMP,'DD-MON-YY HH.MI.SS AM') "Local Time",
to_char(A.TIMESTAMP + interval '4' hour,'DD-MON-YY HH.MI.SS AM') "Server Time",
A.EXTENDED_TIMESTAMP "Time(in UTC)",
A.ACTION_NAME "Action Type",
A.PRIV_USED "Priv Used",
A.USERNAME "DB UserID",
A.OWNER "Obj Owner",
A.OBJ_NAME "Obj Name",
A.SQL_TEXT "SQL Text",
A.RETURNCODE||'|>' "Err code"
from DBA_AUDIT_TRAIL A, V$DATABASE D, V$INSTANCE I
where A.DBID = D.DBID
and D.NAME = I.INSTANCE_NAME
and A.TIMESTAMP between to_date('30-APR-14') and (SYSDATE - interval '4' hour)
order by A.EXTENDED_TIMESTAMP desc ;
thank you very much
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
