Display a formatted time difference

This is a simple query to display the difference between two dates in a formatted way.

Let's assume a given date in a default DD-MON-RR format like this:

select sysdate - to_date('08-OCT-75') from dual;
SYSDATE-TO_DATE('08-OCT-75')
----------------------------
13084.4081

Define a Variable which will hold the number in days and fraction of days

define DateDay = 13084.4081

Then the query

SELECT
TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);


and you've got your formatted output.

YEARS MONTHS DAYS HO MI SE
---------- ---------- ---------- -- -- --
35 9 27 09 47 40