Re: Formatting count of seconds to date format using SQL?

Hi Thomas.

Thanks for your reply, it almost fit the bill.. but not quite: What I want is the number of seconds transformed into the count of days, hours, minutes and seconds that number represents.

Using your suggestion :
  select to_char(trunc(to_date('01 00:00:00', 'DD HH24:MI:SS' )) + (140*(1/24/60/60) ), 'DD HH24:MI:SS' )
  from dual

I get (of course) '01 00:02:20', which is correct except for the '01' at the beginning.

.. or is there yet another smart way to handle this?

PS: If you wonder where I'm using it, it's in an ancient report done in Reports 2.5 that is
being upgraded. Is there maybe some functionality in this tool I haven't found, that could be
of any use?

-- 
Jan-Helge Bergesen +4755225759 
Protek TELsoft


Thomas Kyte wrote:


> 

> A copy of this was sent to Jan-Helge Bergesen <jhb_at_protek.no>


> (if that email address didn't require changing)

> On Mon, 13 Dec 1999 19:28:27 +0100, you wrote:

> 

> >Hello!

> >

> >Is there a way to format a count of seconds into a format like 'DD

> >HH:MI:SS' ?

> >

> >I'm a bit frustrated here...

> 

> a count of seconds since when?  If you know when the seconds are from it is as

> easy as:

> 

> select to_char( WHENCE + (SECONDS * (1/24/60/60)), 'DD HH24:MI:SS' ) from dual;

> 

> If it is the numer of seconds since midnight for example:

> 

>   1* select to_char( trunc(sysdate) + (50029*(1/24/60/60)), 'DD HH24:MI:SS' )

> from dual

> tkyte_at_8i>

> 

> TO_CHAR(TRU

> -----------

> 13 13:53:49

> 

> will get it for you.

> 

> --

> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...

> Current article is "Part I of V, Autonomous Transactions" updated June 21'st

> 

> Thomas Kyte                   tkyte_at_us.oracle.com

> Oracle Service Industries     Reston, VA   USA

> 

> Opinions are mine and do not necessarily reflect those of Oracle Corporation