CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
braumer
braumer

Reputation: 85

PL/SQL: TO_CHAR show format

So I have this code here:

create or replace FUNCTION calc_length(
  START_TIME  IN number, 
  FINISH_TIME IN number
) RETURN NUMBER IS
BEGIN
  RETURN  ( (FINISH_TIME - START_TIME )   ;
END

And I want to show the result in the format as H:mm

I tried TO_CHAR function but it accepts a strict preset formats.

Upvotes: 1

Views: 438

Answers (1)

Art
Art

Reputation: 5792

Few examples - copy, paste to see the oputput:

SELECT trunc(mydate / 3600) hr
     , trunc(mod(mydate, 3600) / 60) mnt
     , trunc(mod(mydate, 3600) / 60 /60) sec
 FROM 
 (
  SELECT (to_date('01/02/2013 23:00:00', 'mm/dd/yyyy  hh24:mi:ss') -
          to_date('01/01/2013 07:00:00', 'mm/dd/yyyy  hh24:mi:ss')) * 86400 mydate
    FROM dual
 )
 /

 Select hh, mi, ss From
 (
  Select EXTRACT(hour   From Cast(SYSDATE as timestamp)) hh,
         EXTRACT(minute From Cast(SYSDATE as timestamp)) mi,
         EXTRACT(second From Cast(SYSDATE as timestamp)) ss
   From dual
  )
  /

Select start_date, end_date, time_diff,
   EXTRACT(DAY FROM time_diff) days,
   EXTRACT(HOUR FROM time_diff) hours,
   EXTRACT(MINUTE FROM time_diff) minutes,
   EXTRACT(SECOND FROM time_diff) seconds
  From
  (
   Select start_date, end_date, end_date - start_date time_diff
     From
      (
       Select CAST(to_date('21/02/2012 06:10:53 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) end_date
            , CAST(to_date('21/02/2012 12:05:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) start_date
     From dual
      ))
     /

Upvotes: 1

Related Questions