1. Registered User
Join Date
Oct 2003
Posts
83

How can I calculate a definite time interval in a day?Means I have to calculate the difference of '2003-10-28 00:48:21.230' and '2003-10-28 00:48:31.230' which will yield 00.10.000

2. Registered User
Join Date
Oct 2003
Location
Dallas
Posts
76

diff

well you can use this as a starter, gets you the diff in minutes, but you can use hour, or seconds(ss).

declare @t as datetime
set @t = '2003-10-27 11:55:52.263'

SELECT DATEDIFF(n, @t, getdate()) AS diff_in_minutes

3. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
I wrote a function to return elapsed time, maybe it will help you.

Code:
```create function 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```

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•