Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:
-- Vishal -
DECLARE
@startTime DATETIME
DECLARE
@endTime DATETIME
SET
@startTime =
'2013-11-05 12:20:35'
SET
@endTime =
'2013-11-10 01:22:30'
SELECT
[DD:HH:MM:SS] =
CAST
((DATEDIFF(
HOUR
, @startTime, @endTime) / 24)
AS
VARCHAR
)
+
':'
+
CAST
((DATEDIFF(
HOUR
, @startTime, @endTime) % 24)
AS
VARCHAR
)
+
':'
+
CASE
WHEN
DATEPART(
SECOND
, @endTime) >= DATEPART(
SECOND
, @startTime)
THEN
CAST
((DATEDIFF(
MINUTE
, @startTime, @endTime) % 60)
AS
VARCHAR
)
ELSE
Best WordPress Hosting, GO link
CAST
((DATEDIFF(
MINUTE
, DATEADD(
MINUTE
, -1, @endTime), @endTime) % 60)
AS
VARCHAR
)
END
+
':'
+
CAST
((DATEDIFF(
SECOND
, @startTime, @endTime) % 60)
AS
VARCHAR
),
[StringFormat] =
CAST
((DATEDIFF(
HOUR
, @startTime, @endTime) / 24)
AS
VARCHAR
) +
' Days '
+
CAST
((DATEDIFF(
HOUR
, @startTime, @endTime) % 24)
AS
VARCHAR
) +
' Hours '
+
CASE
WHEN
DATEPART(
SECOND
, @endTime) >= DATEPART(
SECOND
, @startTime)
THEN
CAST
((DATEDIFF(
MINUTE
, @startTime, @endTime) % 60)
AS
VARCHAR
)
ELSE
CAST
((DATEDIFF(
MINUTE
, DATEADD(
MINUTE
, -1, @endTime), @endTime) % 60)
AS
VARCHAR
)
END
+
' Minutes '
+
CAST
((DATEDIFF(
SECOND
, @startTime, @endTime) % 60)
AS
VARCHAR
) +
' Seconds '
Result Set:
DD:HH:MM:SS StringFormat
4:13:2:55 4 Days 13 Hours 2 Minutes 55 Seconds
(1 row(s) affected)
[UPDATE 2020] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.
Leave a Reply