-- 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


-- Convert minutes to days, hours and minutes using FLOOR/MOD

SELECT
  tot_min  minutes
 ,FLOOR(tot_min/60/24) days
 ,FLOOR(MOD(tot_min,60*24)/60) hours
 ,MOD(tot_min,60)  mins
FROM  (SELECT   &i_min tot_min FROM DUAL)
;




-- NUMTODSINTERVAL function

SELECT
  NUMTODSINTERVAL(5,'DAY')     day_val
 ,NUMTODSINTERVAL(5,'HOUR')    hr_val
 ,NUMTODSINTERVAL(5,'MINUTE')  min_val
 ,NUMTODSINTERVAL(5,'SECOND')  sec_val
FROM DUAL
;

-- SOUNDEX function (phonetic string matching)

SELECT
  qt.idx  ,qt.fname
FROM
(SELECT  1 as idx, 'John' as fname FROM DUAL UNION
 SELECT  2 as idx, 'Jonn' as fname FROM DUAL UNION
 SELECT  3 as idx, 'Kenny' as fname FROM DUAL UNION
 SELECT  4 as idx, 'Johann' as fname FROM DUAL
) qt
WHERE  SOUNDEX(qt.fname) = SOUNDEX('JOHN')
;

 Calendar Setup Queries

 Oracle Functions