convert seconds to HH:MI:SS for large numbers

[NIckman]

hi
I have a column name chk_time number(38).
It is recorded in seconds. I need to view in DAY:HH:MI:SS format or
HH:Mi:SS format. MY most of the numbers are larger, I think i need
day:hh:mi:ss format.

when i run this query

select to_char(to_date(chk_time),'sssss'),'hh24:mi:ss') from dual;

i got his error.

ORA-01830: date format picture ends before converting entire input string

if chk_time is smaller number the same query works. if it is a larger number like 876543298 it is not working. how to solve this pl help. thx,N.

[PAVB]

Either you posted the wrong query or you copy/pasted the wrong error.

SQL> 
SQL> declare
  2  chk_time number;
  3  begin 
  4  chk_time := 600;
  5  select to_char(to_date(chk_time),'sssss'),'hh24:mi:ss') from dual;
  6  end;
  7  /
end;
    *
ERROR at line 6:
ORA-06550: line 5, column 55:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

[NIckman]

Hi
I pasted wrong one.
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 876543298 it is not working. how to solve this pl help. thx,N.

for larger numbers, I got his error.

ORA-01830: date format picture ends before converting entire input string

[tamilselvan]

What is this number 876543298? Is it a time elapsed since Jan 1, 1971?
How did you compute seconds?

[NIckman]

Hi
I just put the large number in seconds.
for example, if the numberof seconds are 773225 or 899578.
I am adding these seconds and convert into hh:mi:ss.
thx,N.

[tamilselvan]

A day = 86400 seconds.

[NIckman]

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.

[mallangouda]

hi , plz try this
select trunc(876543298/(24*60*60))||':'||to_char(to_date(876543298-trunc(876543298/(24*60*60))*86400,'sssss'),'hh24:mi:ss') "day :hh:mi:ss" from dual