Looks like a pretty efficient technique. Thanks for the added value.
But you do need to be careful about one thing though:
Msg 535, Level 16, State 0, Line 5
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Thanks for the feedback Dwain. This "solution" was a little off the topic of your original post so my apologies for the conversation creep. I've been reading your recent articles regarding gaps and islands and how some of that logic can be utilized for other purposes such as your spackle post here. We have some cases at work where these ideas should prove useful over current methods so thank you for providing and helping spur new ways of approach.
As for my original post I freely admit it hadn't been thoroughly tested for longer date ranges. It was a first crack and mainly I didn't anticipate (nor wish for) anyone being in the hospital for over 68 years. Assumptions were made, data was hurt... That said, mainly for my own sake here's a slightly adjusted version which should allow inputs for any dates between 1753 and 9999 for either the start or end dates. Granted it's a specific solution for a specific situation but it was a mildly intriguing thought exercise for the end of the week.
DECLARE @StartTime datetime, @EndTime datetime
SELECT@StartTime = '01/01/1753 00:00:00 AM',
@EndTime = '12/31/9999 11:59:59 PM'
CASE WHEN D.Days = 0 THEN '' ELSE CONVERT(varchar,D.Days) + ' Days, ' END
+ CONVERT(varchar,H.Hours) + ' Hours, '
+ CONVERT(varchar,M.Minutes) + ' Minutes, '
+ CONVERT(varchar,M.Seconds) + ' Seconds'
FROM( VALUES ( DATEDIFF(D, @StartTime, @EndTime), DATEADD(D, DATEDIFF(D, @StartTime, @EndTime), @StartTime) ) ) AS Adj(InitialDays, AdjStartTime)
CROSS APPLY ( VALUES (Adj.InitialDays + CASE
WHEN Adj.AdjStartTime > @EndTime AND @StartTime < @EndTime THEN -1
WHEN Adj.AdjStartTime < @EndTime AND @StartTime > @EndTime THEN 1 ELSE 0 END) ) AS D(Days)
CROSS APPLY ( VALUES (DATEDIFF(S, DATEADD(D, D.Days, @StartTime), @EndTime)) ) AS T(TimeRemaining)
CROSS APPLY ( VALUES (T.TimeRemaining / 3600, T.TimeRemaining % 3600) ) AS H(Hours, TimeRemaining)
CROSS APPLY ( VALUES (H.TimeRemaining / 60, H.TimeRemaining % 60) ) AS M(Minutes, Seconds)