Posted By

bbrumm on 02/22/17

Tagged

sql
Oracle

Versions (?)

Last Edited at 02/22/17 09:27am

Statistics

Viewed 715 times

Favorited by 0 user(s)

Related snippets

Oracle NUMTODSINTERVAL Function

/ Published in: SQL

A few examples of the NUMTODSINTERVAL function.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

--Example 1
SELECT NUMTODSINTERVAL(14, 'DAY')
FROM dual;
 
--Example 2
SELECT NUMTODSINTERVAL(6, 'HOUR')
FROM dual;
 
--Example 3
SELECT NUMTODSINTERVAL(32, 'HOUR')
FROM dual;
 
--Example 4
SELECT NUMTODSINTERVAL(25, 'MINUTE')
FROM dual;
 
--Example 5
SELECT NUMTODSINTERVAL(412, 'MINUTE')
FROM dual;
 
--Example 6
SELECT NUMTODSINTERVAL(51, 'SECOND')
FROM dual;
 
--Example 7
SELECT NUMTODSINTERVAL(8941564, 'SECOND')
FROM dual;
 
--Example 8
SELECT NUMTODSINTERVAL(16.265497, 'SECOND')
FROM dual;
 
--Example 9
SELECT NUMTODSINTERVAL(-18, 'HOUR')
FROM dual;
 
--Example 10
SELECT 
NUMTODSINTERVAL(8941564.59401,'SECOND') AS original_value,
 TO_CHAR
    ( LPAD(EXTRACT(DAY    FROM elapsed_interval),3,'0')||' '||
      LPAD(EXTRACT(HOUR   FROM elapsed_interval),2,'0')||':'||
      LPAD(EXTRACT(MINUTE FROM elapsed_interval),2,'0')||':'||
      LPAD(EXTRACT(SECOND FROM elapsed_interval),8,'0')
    ) AS formatted_seconds
FROM (
  SELECT numtodsinterval(8941564.59401,'SECOND') AS elapsed_interval
  FROM dual
);