convert seconds to HH:MI:SS for large numbers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: convert seconds to HH:MI:SS for large numbers

  1. #1
    Join Date
    Jun 2007
    Posts
    14

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jun 2007
    Posts
    14
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is this number 876543298? Is it a time elapsed since Jan 1, 1971?
    How did you compute seconds?

  5. #5
    Join Date
    Jun 2007
    Posts
    14
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    A day = 86400 seconds.

  7. #7
    Join Date
    Jun 2007
    Posts
    14
    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.

  8. #8
    Join Date
    Apr 2008
    Posts
    6
    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
  •  


Click Here to Expand Forum to Full Width