Re: Timestamp SQL question

Oxnard wrote:
> Oracle 9.2.0.5
>
> Assuming I have only one connection as SYS:
>
>
> select username, sid, serial#, logon_time, to_timestamp(logon_time)
> from v$session
> where to_timestamp(logon_time) = (select to_timestamp(logon_time)
> from v$session where
> username = 'SYS')
> order by logon_time
>
> select username, sid, serial#, logon_time, to_timestamp(logon_time)
> from v$session
> where logon_time = (select logon_time
> from v$session where username = 'SYS')
> order by logon_time
>
>
> I would think that both would return only one row assuming no user logged on
> at the exact same time as SYS. However, the SQL using the timestamp returns
> all rows of v$session.
>
> I would normally just use date and be done with it, but the SQL is being
> used in JAVA code. I need the time and a date object does not seem to do it.
>
> What am I doing wrong? How can I compare timestamps in a where clause?

set linesize 121
col LOT format a20
col username format a20
col program format a20

select program, username, sid, logon_time, to_timestamp(logon_time) LOT from v$session
where to_timestamp(logon_time) = (

   select to_timestamp(logon_time)
   from v$session
   where username = 'SYS')
order by logon_time
/

Does not restrict returned rows to those where username = SYS

Take a look at your query after I modified it ... specifically the PROGRAM column values.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)