-
convert seconds to HH:MI:SS for large numbers
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
Code:
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.
-
Either you posted the wrong query or you copy/pasted the wrong error.
Code:
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi
I pasted wrong one.
this is the query i am running.
Code:
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
-
What is this number 876543298? Is it a time elapsed since Jan 1, 1971?
How did you compute seconds?
-
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.
-
-
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.
Code:
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|