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
;
--Jeff Moden
Change is inevitable... Change for the better is not.