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.
