Re: subtracting date / time in SQL

Syltrem wrote:

> I am trying to calculate the elapsed time for a connected session
> Example:
> select sysdate - logon_time from v$session where sid=35;
>
> SYSDATE-LOGON_TIME
> ------------------
> ,278344907
>
> Which is approximately 6 hours (a quarter of a day).
>
> Now I want to display this in time format, I tried the following
> select to_char(sysdate - logon_time, 'HH:MI:SS') from v$session where
> sid=35
> *
> ERROR at line 1:
> ORA-01481: invalid number format model
>
> and also this
> select to_char(to_date(sysdate - logon_time), 'HH:MI:SS') from v$session
> where sid=35;
>
> What am I doing wrong?
>
> Thanks for helping me!
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site)
> To reply to myself directly, remove .spammenot from my address
>
>
>

Subtracting two dates gives a number - if the result is

xxx.yyy

then xxx is the days, 0.yyy is the part of a day, so 24 * 0.yyy is hours, and so forth

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."