SQL: get DATEDIFF to not return negative values

I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have:

SELECT startTime, endTime,
cast(datediff(hh,starttime,endtime) as varchar)
+':'
+cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as varchar) AS RUNTIME
FROM applog
WHERE runID = 33871
ORDER BY startTime DESC

When I execute this I get expected values and also some unexpected. For example, if starttime = 2008-11-02 15:59:59.790 and endtime = 2008-11-02 19:05:41.857 then the runtime is = 4:-54. How do I get a quere in MS SQL SMS to return the value 3:06 for this case?

Thanks.

Eoin Campbell's I selected as the answer is the most bulletproof for my needs. David B's is do-able as well.

From stackoverflow

• Here's a way to do it:

  -- Find Hours, Minutes and Seconds in between two datetime
  DECLARE @First datetime
  DECLARE @Second datetime
  SET @First = '04/02/2008 05:23:22'
  SET @Second = getdate()
  
  SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
  DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
  DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds
  

  David B : That works great if you are thinking in terms of calendar dates, but all those answers are in "day-sized" chunks. 3:06 is clearly not a day-sized chunk.

• You should separate your calculation and presentation logic:

  DECLARE @applog TABLE
  (
    runID int,
    starttime datetime,
    endtime datetime
  )
  
  INSERT INTO @applog (runID, starttime, endtime)
  SELECT 33871, '2008-11-02 15:59:59.790', '2008-11-02 19:05:41.857'
  -------------------
  SELECT
    SUBSTRING(convert(varchar(30), DateAdd(mi, duration, 0), 121),
    12, 5) as prettyduration
  FROM
  (
  SELECT starttime, DateDiff(mi, starttime, endtime) as duration
  FROM @applog
  WHERE runID = 33871
  ) as sub
  

  If you need to represent more than 24 hours, you would use a different presentation logic. This is just what I could think of fastest.

• Try these

  Assuming 2 declared dates.

  declare @start datetime
  set @start = '2008-11-02 15:59:59.790'
  
  declare @end datetime
  set @end = '2008-11-02 19:05:41.857'
  

  This will return the hours / mins / seconds

  select 
      (datediff(ss, @start, @end) / 3600), 
      (datediff(ss, @start, @end) / 60) % 60,
      (datediff(ss, @start, @end) % 60) % 60
  
  --returns
  
  ----------- ----------- -----------
  3           5           42
  

  This is the zero-padded concatenated string version

  select
  RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 3600)), 2) + ':' +
  RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 60) % 60), 2) + ':' +
  RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) % 60) % 60), 2)
  
  --------
  03:05:42
  

  Sander Versluys : Super! Exactly what i needed! Thanks!

• You need to be consistent with your calls to datediff(). They should all use the same datepart argument.

  See MSDN's DATEDIFF (Transact-SQL) article.

  In your example, you're using both "mi" and "hh" and concatenating.

  Choose the least common denominator for your durations (probably ss or s) and do any math based on that (as the other answers are illustrating, but not really describing).