Since there was some interest in correctly calculating the duration even if the start and end dates were reversed, here's a function that straightens that out. Personally, I'd much rather ensure that the data in the table were actually correct or that an application was passing the dates correctly but this "iSF" function would do just fine (it's only a little slower than the original code) if such things were not possible.

CREATE FUNCTION dbo.Duration

/**********************************************************************************************************************

Purpose:

Given a start and end date as a DATETIME, regardless of the temporal order, calculate the duration between the lowest

datetime and the highest and format the output as HHH:MI:SS.nnn WHERE "HHH" is any number of hours up to the maximum

maximum difference that DATEDIFF and CONVERT can handle (MAX date of 9999-01-01 23:59:59.000 or 71,003,135 hours or

3ms less than 10,000 years).

Obviously, if either value is NULL, a NULL will be returned.

Usage Example:

SELECT ca.FormattedDuration

FROM #JBMTest tt

CROSS APPLY dbo.Duration(tt.StartDT, tt.EndDT) ca

Ref: http://www.sqlservercentral.com/articles/T-SQL/103343/

Revision History:

Rev 00 - 16 Jan 2014 - Jeff Moden - Initial creation and unit test.

**********************************************************************************************************************/

(

@StartDT DATETIME

,@EndDt DATETIME

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH cteSortDates AS

(--==== Sort the 2 dates with a "short circuit" in the CASEs for the expected order.

SELECT StartDT = CASE WHEN @StartDT <= @EndDT THEN @StartDT ELSE @EndDT END

,EndDT = CASE WHEN @StartDT <= @EndDT THEN @EndDT ELSE @StartDT END

)

SELECT FormattedDuration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

FROM cteSortDates

;