This content has been marked as final. Show 2 replies
1. to avoid comparing directly DATE and VARCHAR2 column even if Oracle makes automatic type conversion
2. to remove hour/minute/seconds from DATE data type with SQL TRUNC function if you want to compare only with day/month/year:
SQL> alter session set nls_date_format='DD/MM/YY'; Session altered. SQL> select sysdate from dual; SYSDATE -------- 02/02/13 SQL> select count(sql_id) 2 from v$active_session_history 3 where sql_exec_start = to_date('02/02/13'); COUNT(SQL_ID) ------------- 0 SQL> select count(sql_id) 2 from v$active_session_history 3 where trunc(sql_exec_start) = to_date('02/02/13'); COUNT(SQL_ID) ------------- 321
thank for any help
What is it you need help with?
Your last query showed the proper way to compare a DATE column such as 'SQL_EXEC_START'.
But since DATE columns can include time and your date literal has no time component you will likely not find any exact matches; which your last query also shows.
Just use TRUNC(SQL_EXEC_START) for the comparison.
See V$ACTIVE_SESSION_HISTORY in the Database reference
SQL_EXEC_START DATE Time when the execution of the SQL started
Note that the doc says 'DATE Time' and not just date.