How do I convert a timespan, in seconds, to HH:MM:SS?

Often you want to compare two dates and/or times and express the difference in HH:MM:SS. Except there is no decent way to express the relationship this way; the built-in DATEDIFF function returns a single integer (seconds, minutes, hours, days, months, years, etc).

Here is a way in T-SQL to accomplish this:

DECLARE @d1 DATETIME, @d2 DATETIME, @sd INT

SET @d1 = ‘20050812 05:32:45’
SET @d2 = ‘20050817 02:15:46’

SET @sd = DATEDIFF(SECOND, @d1, @d2)

SELECT [HH:MM:SS] =
CASE WHEN @sd/3600<10 THEN ‘0’ ELSE ” END
+ RTRIM(@sd/3600)
+ ‘:’ + RIGHT(‘0’+RTRIM((@sd % 3600) / 60),2)
+ ‘:’ + RIGHT(‘0’+RTRIM((@sd % 3600) % 60),2)

Here it is in VBScript:

<%
Function TimeSpan(dt1, dt2)
If (isDate(dt1) And IsDate(dt2)) = false Then
TimeSpan = “00:00:00”
Exit Function
End If

seconds = Abs(DateDiff(“S”, dt1, dt2))
minutes = seconds 60
hours = minutes 60
minutes = minutes mod 60
seconds = seconds mod 60

if len(hours) = 1 then hours = “0” & hours

TimeSpan = hours & “:” & _
RIGHT(“00” & minutes, 2) & “:” & _
RIGHT(“00” & seconds, 2)
End Function

d1 = “2002-03-27 9:20:25 AM”
d2 = “2002-03-27 9:20:45 AM”

Response.Write TimeSpan(d1, d2)
%>