SQL: get DATEDIFF to not return negative values

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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).