0

I need to get Difference between two dates as HH:MM:SS format I tried this

Select UserId,StartTime,EndTime,   DATEDIFF(HOUR,   StartTime, EndTime) AS  Hours,
 DATEDIFF(MINUTE, StartTime, EndTime) AS  Minutes,
  DATEDIFF(SECOND, StartTime, EndTime) AS  Seconds 

from Responses

but it didn't work well because it calculates hours , minutes and seconds Separately. For example: if the minutes greater than 60 minutes it won't change the hour

1:70:00 instead of 2:00:00

I need when the minutes greater than 60 minutes adds a new hour and when the sconds greater than 60 sconds add a new minutes

Thank you,

SomeDeveloper
  • 11
  • 1
  • 1

3 Answers3

2
declare @D1 datetime = '1jan2016 12:02:55'
declare @D2 datetime = '5jan2016 04:11:35'
Select @D2 - @d1, 
    Format(datediff(second, 0, @D2 - @d1)/3600, '0') + ':' +
    Format(datediff(second, 0, @D2 - @d1)%3600 / 60, '00') + ':' +
    Format(datediff(second, 0, @D2 - @d1)%3600 % 60, '00')
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • It's working, thank you so much,Can you please show me how can i do the avoid t-sql with Time DataType not string? Thank you – SomeDeveloper Oct 20 '16 at 16:00
0

You can try this way:

SELECT Userid, StartTime, EndTime,  convert(time(0),StartTime - EndTime) AS [Time in hh:mm:ss]
FROM Responses
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0

You can use the cast function to do this instead of DATEDIFF,something like

declare @StartDate datetime, @EndDate datetime

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

Select CAST((@EndDate-@StartDate) as time(0)) '[hh:mm:ss]'

`

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
viswanath n
  • 21
  • 1