Oracle: Dates Difference in days, hours, minutes & seconds

SELECT 
    trunc(DATE1-DATE2) days,
    to_char(trunc(sysdate) + (DATE1-DATE2), 'HH24') HOURS,
    to_char(trunc(sysdate) + (DATE1-DATE2), 'MI') MINUTES,
    to_char(trunc(sysdate) + (DATE1-DATE2), 'SS') SECONDS
FROM DUAL;


Related Posts:
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Date Difference in Days, Months and Years
- ORA-01830 date format picture ends before converting entire input string
- Oracle: Convert seconds to time - hh:mi:ss
- Oracle: convert time hh:mi:ss to seconds

3 comments:

  1. This is good too (By Anuj Sir):

    SELECT
    trunc(DATE1-DATE2) days,
    mod( trunc( ( DATE1-DATE2 ) * 24 ), 24) HOURS,
    mod( trunc( ( DATE1-DATE2 ) * 1440 ), 60 ) MINUTES,
    mod( trunc( ( DATE1-DATE2 ) * 86400 ), 60 ) SECONDS
    FROM dual;

    ReplyDelete
  2. I would like to know just i have starting date and ending date i want to know how to minus ending date from starting date and show it in a total column.

    ReplyDelete