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

From: <pberetta_at_my-deja.com>
Date: Tue, 14 Dec 1999 12:08:09 GMT
Message-ID: <835bv7$u0o$1_at_nnrp1.deja.com>

Jan-Helge,
  Perhaps this is what you are looking for. This is a function to accept two dates and return the days/hours/mins/seconds between them - if you are working with a column representing a number of seconds directly, you can modify it ---

CREATE OR REPLACE FUNCTION DAYS_BET(v_dt1 DATE, v_dt2 DATE)   RETURN varchar2 IS v_output varchar2(100);

  v_sec     number(10);
  v_full_d  number(10);
  v_part_d  number(10);
  v_full_h  number(10);
  v_part_h  number(10);
  v_full_m  number(10);
  v_part_m  number(10);

BEGIN

  v_sec := (v_dt1 - v_dt2) * 84600;
  v_full_d := trunc(v_sec / 84600);
  v_part_d := mod(v_sec,84600);
  v_full_h := trunc(v_part_d / 3600);
  v_part_h := mod(v_part_d,3600);
  v_full_m := trunc(v_part_h / 60);
  v_part_m := mod(v_part_h, 60);
  v_output := to_char(v_full_d) || ' days ' || to_char(v_full_h) || '
hrs. '
              || to_char(v_full_m) || ' mins. ' || to_char(v_part_m)

|| ' secs.';
  RETURN v_output;
END;
/

Here's a sample of the output:

SQL> SELECT TO_CHAR(hiredate,'MM/DD/RRRR HH24:MI:SS'),

  2          TO_CHAR(sysdate,'MM/DD/RRRR HH24:MI:SS'),
  3          DAYS_BET(sysdate, hiredate)

  4 FROM emp
  5 WHERE empno = 8000;

TO_CHAR(HIREDATE,'M TO_CHAR(SYSDATE,'MM DAYS_BET(SYSDATE,HIREDATE)

------------------- ------------------- --------------------------------

09/30/1999 09:00:00 12/14/1999 07:05:48 74 days 21 hrs. 38 mins. 11 secs.

Regards,
Paul

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 13:08:09 CET