Oracle Functions - To Convert Seconds to Days, Hours, Minutes and Seconds
-- Oracle functions to convert seconds into days, HH:MM:SS format 
-- (e.g. 92546 => "1, 01:42:26")

WITH q_time AS
(SELECT   i_val,
   FLOOR(i_val/86400) days,
   CEIL(i_val/60/60) max_Hrs,
   FLOOR(MOD(i_val,86400)/3600) Hrs,
   FLOOR(MOD(i_val,60*60)/60) Min_,
   MOD(FLOOR(MOD(i_val,60*60)),60) Sec_
 FROM  (SELECT &i_val i_val FROM DUAL)
)
SELECT   i_val,
 days,  Hrs, Min_, Sec_,
 CAST(days||', '||
      LPAD(Hrs,2,'0')||':'||LPAD(min_,2,'0')||':'||LPAD(sec_,2,'0') 
  AS  VARCHAR2(25)) Days_time_desc 
FROM  q_time; 

=> Time Description Output

DateTimeStamp Functions

DateDiff

Date Queries

Calendar Setup Queries


Oracle registered trademark of Oracle Corporation.

Last Revised On: October 13th, 2015

  342