December 28, 2011 at 5:09 pm
I am trying to get the difference of dates in HH:MM only format. The issues are that there could be several days difference and I would still like to see the difference in HH:MM format.
EX: DIFF between 01/01/2011 12:01:00:00 and 01/05/2011 15:31:02:55 would show as 96:30.
With that I am going to add to a report that shows an average; therefore the many x days HH:MM examples are not useful.
Has anyone done this?
This is what I have tried to do with a little success but there are flaws if the value is less than an hour:
Examples :
2011-12-22 15:20:00.000 and 2011-12-22 16:03:09.000 show as 1:43 (should be 0:43)
2011-12-22 15:31:00.000 and 2011-12-22 16:00:56.000 show as 1:0 (should be 0:30)
BUT
2011-12-05 16:34:00.000 and 2011-12-22 15:56:23.000 show 407:22 (correct)
convert(varchar, Datediff(hh,'2011-12-22 15:20:00:000', '2011-12-22 16:03:09:000') ,108)
+':'+
convert(varchar,Datediff(n,'2011-12-22 15:20:00:000', '2011-12-22 16:03:09:000'), '2011-12-22 16:03:09:000')%60) HHMM
Not sure if I should cast as a smalldatetime before datediff or what. Any help would be greatly appreciated.
December 28, 2011 at 6:28 pm
try this one..
declare @startDate datetime
declare @endDate datetime
set @startDate = '2011-12-22 15:20:00.000'
set @endDate = '2011-12-22 16:03:09.000'
select convert(varchar(30), (datediff(mi, @startDate, @endDate) / 60))
+ ':' +
convert(varchar(30), (datediff(mi, @startDate, @endDate) % 60))
"Often speak with code not with word,
A simple solution for a simple question"
December 30, 2011 at 4:44 pm
Found a good example after some more reading and a few examples.
CONVERT(varchar, DATEDIFF(n, @start, @end)/60) + ':'
+ RIGHT('0' + CONVERT(varchar, (DATEDIFF(n, @start, @end)%60)), 2)
To round to the nearest minute cast @start and @end to a SMALLDATETIME for the minutes DATEDIFF only. This will give you the chance to round up to the next minute if the seconds are greater than 30.
Thanks for the help and suggestions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply