-5

please let me know how to calculate date difference and display in days and hour format in SQL. The table and required result is given in the attached screenshot. Please refer the same.

enter image description here

Ross Presser
  • 6,027
  • 1
  • 34
  • 66
rinson jimmy
  • 37
  • 5
  • I dont think this is a duplicate question, the other question just gives me in hour and minute format. I need it in days format also. – rinson jimmy Mar 21 '19 at 20:01
  • 1
    Here's a link that may help you. http://sqlfiddle.com/#!4/82052/3/0 – Ross Presser Mar 21 '19 at 20:08
  • If you have a moment, could you post your ultimate solution as an answer? I was getting some funny answers form the sqlfiddle, and I have no Oracle server available, so I didn't want to add one myself. @rinson-jimmy – Ross Presser Mar 26 '19 at 20:18
  • EXTRACT(DAY FROM (END_TIME - START_TIME) DAY TO SECOND) || 'D ' || EXTRACT(HOUR FROM (END_TIME - START_TIME) DAY TO SECOND) || 'H ' || EXTRACT(MINUTE FROM (END_TIME - START_TIME) DAY TO SECOND) || 'M' "Duration" - this worked for me – rinson jimmy Mar 27 '19 at 21:28

2 Answers2

0

You can use this provided your date is in the DB datetime format

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF(T1.START, T1.END)) / 24), ' D ',
MOD(HOUR(TIMEDIFF(T1.START, T1.END)), 24), ' H ',
MINUTE(TIMEDIFF(T1.START, T1.END)), ' M')
FROM TABLE1 T1
Aaron Pereira
  • 314
  • 1
  • 2
  • 10
0
SELECT 
    (Cast(DATEDIFF(hour, start, end) as varchar) + 
        'H ' +
         Cast(DATEDIFF(minute, start, end)as varchar)) AS Duration;, 
    ID 
from table1;

Or

SELECT 
    (Cast(DATEDIFF(hour, t.start, t.end) as varchar) + 
        'H ' +
        Cast(DATEDIFF(minute, t.start, t.end)as varchar)) AS Duration;, 
    ID 
from table1 t;
Shmiel
  • 1,201
  • 10
  • 25
VIVEK SANWAL
  • 1
  • 3