Friday, February 17, 2012

DateDiff

I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that??
tia,
e3wittI'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP|||thanks! that works like a charm....|||I'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP

[shameless post]

Thank GOD!

Someone is taking all the difficult questions...

Where would we be otherwise?

OH that's right x002548 is awol

Now will somone PLEASE fix me a 'rita

[/shameless post]|||This is the code PaulYoung posted some time ago, as as such became public domain.
if object_id('dbo.fn_ElapsedTime') is not null
drop function dbo.fn_ElapsedTime
go
/*
************************************************** **************
Created : 06/01/2004
By : Paul Young (dbForums participant)
Purpose : Return elapsed time in 99:99:99.999 format.
Execute : select dbo.fn_ElapsedTime(@.date_start, @.date_end)
************************************************** **************
*/
create function dbo.fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null) returns varchar(40)
as begin
declare @.d int,
@.h int,
@.m int,
@.s int,
@.ms int,
@.dif1 int,
@.ret varchar(40)
select @.d = 0,
@.h = 0,
@.m = 0,
@.s = 0,
@.ms = 0
set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + '.' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end

No comments:

Post a Comment