Rudy Rodarte . com

Information

This article was written on 05 May 2011, and is filled under SQL Blogs.

Current post is tagged

, ,

Using datediff to display results as HH:MM:SS

DateDiff, y u no Pretty?

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.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply