It's taken me a while, and I'll be this isn't the most efficient way of doing it (too many conversions), but I have an XML solution working which someone might be able to improve on. Note that since you are dealing with times I have used Date functions - but make sure you read the caveat at the end. Basically, in your CTE you convert the data to simple XML form like
<Note>
<T1>00:00</T1>
<T2>00:00</T2>
</Note>
This takes away the need to do string splitting functions. Now you can get your result by using a combination of DATEDIFF, DATEADD, CAST and LEFT
WITH CTE1 AS
(
SELECT CAST('<Note><T1>' + REPLACE((LTRIM(RTRIM(REPLACE(CAST(Note AS NVARCHAR(4000)),
':tt:','')))), ',', '</T1><T2>') + '</T2></Note>' AS XML) AS Note
FROM UD_Notes
WHERE Note like ':t%'
)
SELECT LEFT(CAST(DATEADD(mi, DATEDIFF(mi, CAST(y.T1 AS TIME), CAST(y.T2 AS TIME)), CAST('00:00' AS TIME)) AS VARCHAR(20)), 5)
FROM (
SELECT c.value('(T1/text())[1]', 'varchar(50)') AS T1,
c.value('(T2/text())[1]', 'varchar(50)') AS T2
FROM (SELECT Note FROM CTE1) x
CROSS APPLY Note.nodes('/Note') AS T(c)) y
What's happening is that first we get the CTE to give us an XML data type which we can then query to give us two 'columns' (T1 and T2). The SELECT is doing a lot of jobs at once so I'll break it down. Lets assume the example you gave which was 12:35 - 12:32 = 00:03.
DATEDIFF(mi, CAST(y.T1 AS TIME), CAST(y.T2 AS TIME))
Here we get the two columns T1 and T2 and we CAST those to the TIME type and get the difference (i.e. T2 - T1). This gives us an INT difference of 3 so in the next bit I'll simply substitute 3 into the relevant part.
So, now we get
DATEADD(mi, 3, CAST('00:00' AS TIME))
What we're doing here is creating a notional TIME of 00:00 and adding our difference to it in minutes (mi) so this gives us 00:03. Once again, we'll substitute it in.
CAST(00:03 AS VARCHAR(20))
Pretty simple here, we're just getting a VARCHAR representation of our time. Because TIME has a pretty good accuracy, we get the seconds and nanoseconds which would look like this 00:03:00.0000000. We want rid of the seconds and nanoseconds since we're dealing with hours and minutes here.
LEFT('00:03:00.0000000', 5)
That leaves us with '00:03' as a string.
I won't bother going through the body of the query because although I can write it I don't think I could explain it quite clearly enough but there's plenty of pages that will explain XML querying far better than I can such as this blog post from MSDN and this blog post which looks at querying XML fields in t-sql.
I have no doubt there's a better way to do this and someone on here can also give a good explanation of the query - please feel free anyone to add to this and explain what I struggle to.
CAVEAT
If T1 was 01:00 and T2 was 12:57 then you would get an answer of 11:57 because they are indeed 11 hours and 57 minutes apart in the forward direction. To be honest I haven't got time now to work out how to make this give you a result of 3 for those two times, but someone else probably can spot how to, I hope.
