Presenting the time/duration in HH:MM:SS format is important for good readability. There are situations where the duration of an event needs to be calculated between time intervals. The difference can be calculated using the built-in function DATEDIFF(). The return value of the function will be based on the datepart unit, such as hours or minutes or seconds, etc., Adding simple math to the output of DATEDIFF() function will produce the result in HH:MM:SS format.
Consider a job that starts at 10:05 AM and finishes at 10:34 AM. How long does it take to complete?
SELECT DATEDIFF(SECOND, '1/1/2018 10:05:00', '1/1/2018 10:36:10')
The difference for the two dates is returned in seconds as 1870. However, this value is not easy to interpret by the human mind. So, presenting the value as 00:31:10 (hours: minutes: seconds) will be of great benefit to the users who read.
There is no built-in function in SQL to produce the result in this format, but adding some math and string concatenation would produce the desired result.
DECLARE @sec INT
SET @sec = DATEDIFF(SECOND, '1/1/2018 10:05:00', '1/1/2018 10:36:10')
SELECT
RIGHT('00' + CAST((@sec)/3600 AS VARCHAR(3)) ,2) + ':' +
RIGHT('00' + CAST((@sec%3600)/60 AS VARCHAR(3)),2) + ':' +
RIGHT('00' + CAST((@sec%3600)%60 AS VARCHAR(3)),2) AS Duration
This script returns the result as 00:31:10 (HH:MM:SS). Only one method is elaborated in this post while there are several other ways to produce the same outcome.