CODEWITHSUNDEEP
sql-serversql-server-2014
Lajith
Lajith

Reputation: 1867

DATE TIME difference between 2 dates

In SQL Server 2014, I need to calculate total time difference between start time and end time in HH::MM::SS format of all records

Sample data:

ID      StartTime               EndTime
---------------------------------------------------
5287    2017-11-04 12:38:04     2017-11-04 12:40:45
5288    2017-11-04 17:08:28     2017-11-04 17:08:34
5289    2017-11-04 17:08:41     2017-11-04 17:11:41

(2:41 --time difference OF ID 5287

0:6 --time difference OF ID 5288

3:0 --time difference OF ID 5289 )

OUTPUT: 00:05:47

Upvotes: 1

Views: 146

Answers (3)

Arockia Nirmal
Arockia Nirmal

Reputation: 777

Test table 

create table timeTest
(
Id int,
StartTime datetime , 
EndTime datetime
)

select * from timeTest

enter image description here

select convert(varchar(5),sum(DateDiff(s, StartTime, 
EndTime)/3600))+':'+convert(varchar(5),sum(DateDiff(s, StartTime, 
EndTime)%3600/60))+':'
+convert(varchar(5),(sum(DateDiff(s, StartTime, EndTime)%60)))  as 
[hh:mm:ss]
from timeTest

OUTPUT:

enter image description here

Upvotes: 1

ARr0w
ARr0w

Reputation: 1731

try something like : SELECT DATEDIFF(year, expression_one, expression_two) AS DateDiff;

the part year is the interval. It can be any of the following:

  • quarter, qq, q = Quarter
  • month, mm, m = month
  • dayofyear = Day of the year
  • day, dy, y = Day
  • week, ww, wk = Week
  • weekday, dw, w = Weekday
  • hour, hh = hour
  • minute, mi, n = Minute
  • second, ss, s = Second
  • millisecond, ms = Millisecond

Upvotes: 1

apomene
apomene

Reputation: 14389

Try something, like:

SELECT CONVERT(char(8), DATEADD(second, DATEDIFF(SECOND,StartTime,EndTime), ''), 114)

And if you want total sum:

SELECT CONVERT(char(8), DATEADD(second, SUM(DATEDIFF(SECOND,StartTime,EndTime)), ''), 114)

Upvotes: 1

Related Questions