Help in Time Calculation

Question

Hi, How I can subtract time from startdate and enddate. I want to get result in 'Duration-time' (Diff of both dates) in dd:hh:mm:ss format.

create table #A (Startdate datetime, Finishdate datetime)

Insert into #A values ('2020-09-09 11:51:39.613','2020-09-09 11:57:39.093')

Select * from #A

Startdate Finishdate Duration-Time-HH:MM:SS

2020-09-09 11:51:39.613 2020-09-09 11:57:39.093

Answer 1

;With cte As
(Select Startdate, Finishdate, DateDiff(second, Startdate, Finishdate) As Diffsec
From #A)
Select Startdate, Finishdate, RIGHT('0' + CONVERT(VARCHAR(12), Diffsec /60/60/24), 2) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), Diffsec /60/60 % 24), 2) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  Diffsec /60 % 60), 2) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  Diffsec % 60), 2) As DiffInddhhmmssFormat    
From cte;

Tom

Answer 2

Hi @Martin Kevin ,

Please also find below method. Thanks.

      --HH:MM:SS format  
select Startdate,Finishdate,STUFF(CONVERT(VARCHAR(20),Finishdate-Startdate,108),1,2,DATEDIFF(hh,0,Finishdate-Startdate)) [Duration-Time-HH:MM:SS] from #A  
          
      --DD:HH:MM:SS format  
SELECT Startdate,Finishdate,cast(DATEDIFF(hour,StartDate,Finishdate)/24 as varchar(10)) +':'  
        + Right('00'+cast(DATEDIFF(hour,StartDate,Finishdate)%24 as varchar(10)) ,2)+ ':'  
        + Right('00'+Cast(DATEDIFF(minute,StartDate,Finishdate) % 60 as varchar(10)) ,2) + ':'  
        + Right('00'+Cast(DATEDIFF(second,StartDate,Finishdate)%60  as varchar(10)),2) AS  [Duration-Time-DD:HH:MM:SS] from #A  

Output:

Startdate Finishdate Duration-Time-HH:MM:SS  
2020-09-01 11:51:39.613 2020-09-09 11:57:39.093 192:05:59  

Startdate	Finishdate	Duration-Time-DD:HH:MM:SS  
2020-09-01 11:51:39.613	2020-09-09 11:57:39.093	8:00:06:00  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Here is one more solution:
create table #A (Startdate datetime, Finishdate datetime)

Insert into #A values ('2020-09-08 11:51:39.613','2020-09-09 13:57:39.093')

; WITH secs AS (
  SELECT datediff(second, Startdate, Finishdate) AS secs
  FROM   #A
)
SELECT concat(secs / 86400, ' ', 
              convert(char(8), dateadd(second, secs, convert(time(0), '00:00:00')), 108))
FROM   secs
go 
DROP TABLE #A

Answer 4

create table #A (Startdate datetime, Finishdate datetime)

Insert into #A values ('2020-09-09 11:51:39.613','2020-09-09 11:57:39.093')



SELECT StartDate, Finishdate
, Coalesce( Convert(varchar(5),DateDiff(day, (Finishdate-StartDate),'1900-01-01')) + ':'
+ Convert(varchar(10),(Finishdate-StartDate), 108),'00:00:00:00') as [days:hh:mm:ss]

-- FORMAT function
,Coalesce( Convert(varchar(5),DateDiff(day, (Finishdate-StartDate),'1900-01-01')) + ':' +
FORMAT((Finishdate-StartDate), 'HH:mm:ss') ,'00:00:00:00') as [days:hh:mm:ss]

from #A

 drop table #A