CODEWITHSUNDEEP
oracle-database
NicoRiff
NicoRiff

Reputation: 4883

Difference between two dates and getting result in timestamp

I´m trying to calculate the difference between two dates in Oracle and getting the result as a TimeStamp. This is the easiest thing to do in SQL Server, but it seems that Oracle does not have a easy way to solve this. I refuse to believe that I have to write that much code to get what I need. Can someone tell me if there is a easier way to get that difference?:

SELECT TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')

I need the result be something like:

enddate = '2017-03-01 17:30:00'
startdate = '2017-03-01 10:00:00'

difference: 07:30:00

Upvotes: 0

Views: 94

Answers (1)

Rene
Rene

Reputation: 10551

Substract the two dates. Add the result to the current date (without any time component, trunc(sysdate)) and show only the time.

select to_char(trunc(sysdate) + (to_date('2017-03-01 17:30:00', 'YYYY-MM-DD HH24:MI:SS') -
                                 to_date('2017-03-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'))
              ,'HH24:MI:SS')
  from dual

Upvotes: 1

Related Questions