How to Convert Seconds to “HH:MM:SS” format in TSQL

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.

SET @sec = DATEDIFF(SECOND, '1/1/2018 10:05:00', '1/1/2018 10:36:10')

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.

Leave a Reply

Your email address will not be published.