Nice one, Paul.

Here are two different one-line TSQL solutions:

;With MyCTE (aTime, bTime)

AS

(

SELECT CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')

UNION ALL SELECT '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'

UNION ALL SELECT '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'

UNION ALL SELECT '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'

UNION ALL SELECT '1900-01-01 00:00:00.000000111', '1900-01-01 00:00:00.000000222'

UNION ALL SELECT '1900-01-01 00:10:00.000000111', '1900-01-01 00:00:00.000000222'

)

SELECT

aTime,

bTime,

SimpleProof = CAST(DATEADD(ms,ABS(DATEDIFF(ms,aTime, bTime)),CAST(CAST(0 AS DATETIME) AS DATETIME2)) AS TIME(3)),

-- Method 1 (Chris R - too shy to post)

TimeResult1= CAST(

DATEADD(

NS,

ABS(CAST(SUBSTRING(CAST(btime as varchar(30)),20,8) AS float) -

CAST(SUBSTRING(CAST(atime as varchar(30)),20,8) AS float)

) *1000000000,

DATEADD(

SS,

ABS(DATEDIFF(SS, aTime, btime)),

CAST('1900-01-01 00:00' AS datetime2))

)

AS time(7)),

-- Method 2 (Chris M)

TimeResult2= CAST(

CONVERT(VARCHAR(8),

DATEADD(second,ABS(DATEDIFF(second,aTime, bTime)),0),108) + '.'

+ RIGHT('000000000'

+ CAST(ABS(

DATEDIFF(ns,

DATEADD(second,DATEDIFF(second,aTime, bTime),aTime),

bTime))

AS VARCHAR)

,9)

AS TIME(7))

From myCTE