Find duration in hh:mm:ss in SQL Server

Question

I have starttime and endtime columns in my table and I want to get time difference between the two. But however I want the difference in hh:mm:ss format which I am not getting.

Declare @starttime = '7/23/2020 3:30:02 PM'
Declare @endtime = '7/23/2020 3:30:07 PM'
Select cast(@starttime - @endtime as Time) As Timedifference

I get 00:00:05.000000 which I don't want.

you can convert the result into char(8) and it would strip the miliseconds.. Depends on what you do with the result afterwards. — Mitz, Jul 31 '20 at 17:44

score 0 · Answer 1 · edited Jul 31 '20 at 15:27

0

This may help you:

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'
      +convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'
      +convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]

edited Jul 31 '20 at 15:27

Thom A

88,727
11
45
75

answered Jul 31 '20 at 14:58

Nikhil Bhoknal

1
1

Gabriele Franco · Answer 2 · 2020-08-01T06:01:08.823

From SQL Server 2012 you can use FORMAT function.

In the following post you can find many solutions of this:

SQL time difference between two dates result in hh:mm:ss

Below the best solution if you have SQL Server 2012 or later version:

--get the difference between two datetimes in the format: 'hh:mm:ss'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
  DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
  DECLARE @difference VARCHAR(10) =
  FORMAT(@seconds / 3600, '00') + ':' +
  FORMAT(@seconds % 3600 / 60, '00') + ':' +
  FORMAT(@seconds % 60, '00')
  RETURN @difference
END

For FORMAT documentation look here

Find duration in hh:mm:ss in SQL Server

2 Answers2