Oracle: How can I get the time difference between two date columns?

Question

SELECT EMP_NAME, SSN FROM EMP WHERE HIER_DATE = TO_DATE('03-NOV-1999','DD-MON-YYYY');

Answer 1

declare @StartDate datetime,
@EndDate datetime

set @StartDate = '10/01/2012 08:40:18.000'
set @EndDate =   '10/04/2012 09:52:48.000' 

select datediff(hour, @StartDate, @EndDate) hr,
   datediff(minute, @StartDate, @EndDate) 
   - datediff(hour, @StartDate, @EndDate) * 60 mins,
   datediff(second, @StartDate, @EndDate) 
   - (datediff(minute, @StartDate, @EndDate) * 60) sec,
   cast(datediff(hour, @StartDate, @EndDate) as varchar)+':'+ 
   cast(datediff(minute, @StartDate, @EndDate) 
   - datediff(hour, @StartDate, @EndDate) * 60 as varchar)+':'+ 
   cast(datediff(second, @StartDate, @EndDate) 
   - (datediff(minute, @StartDate, @EndDate) * 60) as varchar) timediff