Here's a version which corrects for aTime being more recent than bTime, and includes a filter designed to return rows where the algorithm falls over. I've set it up with multiple CROSS APPLY operators as before, because a cascaded CROSS APPLY like this is much easier to understand and debug. Once the code is verified, the cCA can be "compacted down".

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

*

From myCTE a1

CROSS APPLY (SELECT SecondsDifferenceINT = DATEDIFF(second,aTime, bTime)) a

CROSS APPLY (SELECT AdjustedFirstDateDT = DATEADD(second,SecondsDifferenceINT,aTime)) b

CROSS APPLY (SELECT NanosecondRemainderINT = DATEDIFF(ns,AdjustedFirstDateDT,bTime)) c

-- use ABS() to convert seconds difference to a positive number

-- where aTime is more recent than bTime

CROSS APPLY (SELECT TimeToSecondsDT = DATEADD(second,ABS(SecondsDifferenceINT),0)) d

CROSS APPLY (SELECT TimeToSecondsVC = CONVERT(VARCHAR(8),TimeToSecondsDT,108)) e

CROSS APPLY (SELECT ResultVC = e.TimeToSecondsVC + '.'

+ RIGHT('000000000'+CAST(NanosecondRemainderINT AS VARCHAR),9)

) f

CROSS APPLY (

SELECT

TimeResult= CAST(f.ResultVC AS TIME(7)),

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

) g

-- Filter for exceptions - this will return rows where the algorithm fails

-- (if SQL Server doesn't bork first).

WHERE CAST(TimeResult AS DATETIME) <> CAST(SimpleProof AS DATETIME)