Sometimes you just need hh:mm:ss

I ran into a case when I needed to display some results in the HH:MM:SS format. We all know and love DATEDIFF, which gives you whole number results. Using DATEDIFF, I was getting results like this:

--	Declare some variables for the requested timeframe
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME--	Set the timeframe variables
SET		@START_DATE = '2011-01-01 16:00:00'
SET		@END_DATE =   '2011-01-01 22:47:22'
SELECT DATEDIFF(second,@Start_date, @END_DATE) AS Segundos

Segundos
24442

(1 row(s) affected)

The results are all well and good, but that isn’t something that I would want to hand over to the end users. So, I did a bit of hunting and found what I was looking for over on SQLServerCentral.com. The guy wanted to display milliseconds along with the rest of his results. That was overkill so, his script worked perfectly for me. Here is what I ended using:

--	Declare some variables for the requested timeframe
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME--	Set the timeframe variables
SET		@START_DATE = '2011-01-01 16:00:00'
SET		@END_DATE =   '2011-01-01 22:47:22'--	Use datediff to show the results in the
--	HH:MM:SS format
SELECT    CONVERT(varchar(6), DATEDIFF(second, @START_DATE, @END_DATE)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @START_DATE, @END_DATE) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, @START_DATE, @END_DATE) % 60), 2) AS 'HH:MM:SS'

HH:MM:SS
6:47:22

The results come back in a nicer, easily readable format.