Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Question Unanswered: Time Calculation

    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. #2
    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. #3
    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
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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