Home » SQL & PL/SQL » SQL & PL/SQL » convert seconds to HH:MI:SS for large numbers in seconds
convert seconds to HH:MI:SS for large numbers in seconds [message #308004] Thu, 20 March 2008 14:25 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi
this is the query i am running.
select emp_group||','||
to_char(to_date(sum(chktime),'sssss'),'hh24:mi:ss')
from emp,dept
where emp.id = dept.id
and emp_group='finance'
group by emp_group having sum(chktime) > 1;

if chktime is smaller number the same query works. if it is a larger numbers like 876543(seconds) it is not working. how to solve this pl help. thx,N.

for larger numbers in seconds, I got his error.
chktime usually contains large number in seconds in table.
for example, the vlaues are 744856,856243,988243,766429,989785
I need to convert in HH:MI:SS.
and also i need to sum it and convert into HH:MI:SS.
I think, I need use to DAY:HH:MI:SS due to large numbers.
any help. pl.thx.


ORA-01830: date format picture ends before converting entire input string
Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308005 is a reply to message #308004] Thu, 20 March 2008 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are 86400 seconds per day.
Just use TRUNC and MOD and concat the results.

Regards
Michel
Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308013 is a reply to message #308005] Thu, 20 March 2008 15:24 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
hi Michael
I did n,t get it.
I need to convert these numbers in HH:MI:SS.
744856,856243,988243,766429,989785 first
when i do that, i got error msg ORA-01830.
and also i want to run another query using sum(chktime)
and convert into HH:MI:SS.
so, I need to add these numbers 856243,988243,766429,989785.
then convert into HH:MI:SS.
why we have MOD. it will divide the number.
I di dnot get it.
how trunc and mod solve the problem. pl let me kwnow.
thx,N.



Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308018 is a reply to message #308013] Thu, 20 March 2008 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select trunc(856243/86400)||':'||
  1         to_char(to_date(mod(856243,86400),'SSSSS'),'HH24:MI:SS')
  2  from dual
  3  /

9:21:50:43

Regards
Michel

[Updated on: Thu, 20 March 2008 15:44]

Report message to a moderator

Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308131 is a reply to message #308018] Fri, 21 March 2008 06:59 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Thanks michel.
i got the formulae. you are good at math.
thx,N.

Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308139 is a reply to message #308131] Fri, 21 March 2008 09:05 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi
i am sorry these not seconds. these are milliseconds.
so, i need to convert millisecond to HH:MI:SS

so, i used this query.
it is not working.


select emp_group||','||
to_char(to_date(mod(chktime/3600000),'sssss'),'hh24:mi:ss')
from emp,dept
where emp.id = dept.id
and emp_group='finance';


ORA-01830: date format picture ends before converting entire input string
pl help.
thx,N.
Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308140 is a reply to message #308139] Fri, 21 March 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are 86400000 milliseconds in a day and 1000 milliseconds in a second.
Try it, it is the same thing.

Regards
Michel
Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308145 is a reply to message #308139] Fri, 21 March 2008 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with data as (select 856243+988243+766429+989785 time from dual)
select trunc(time/86400000)||':'||
       to_char(to_date(mod(trunc(time/1000),86400),'SSSSS'),'HH24:MI:SS')
       ||'.'||mod(time,1000) d
from data
/

0:01:00:00.700

Regards
Michel
Re: convert seconds to HH:MI:SS for large numbers in seconds [message #308176 is a reply to message #308145] Fri, 21 March 2008 14:01 Go to previous message
NIckman
Messages: 64
Registered: May 2007
Member
Excellant. Thanks.N.
Previous Topic: Procedure for every date between the date period
Next Topic: amiguous definition???
Goto Forum:
  


Current Time: Fri Dec 02 18:24:37 CST 2016

Total time taken to generate the page: 0.22896 seconds