Results 1 to 4 of 4

Thread: datediff

  1. #1
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    8

    Unhappy Unanswered: datediff

    when I create the table I used

    [from_time] [smalldatetime] NULL ,
    [to_time] [smalldatetime] NULL ,
    [total_hrs] AS (datediff(hh,[from_time],[to_time])) ,

    but the hours calculation is comming wrong. moreover i want set the format like hh:mi. but i could not. pls help

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Refer to DATEPART function under books online.#


    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    elams if your goal is to return elapsed time, you might want to change your calculated field to hold minutes or seconds rather than hours, this would make it easier to re-format.

    as an alternative, here is a function that will return elapsed time.

    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
    
    GO
    
    create table #Tmp(
      from_time smalldatetime
    , to_time smalldatetime
    , total_hrs as (datediff(hh,from_time, to_time))
    )
    insert into #Tmp values(getdate(), dateadd(dd,4,getdate()))
    insert into #Tmp values(getdate(), dateadd(hh,2,getdate()))
    insert into #Tmp values(getdate(), dateadd(mi,3,getdate()))
    select from_time
         , to_time
         , total_hrs 
         , dbo.fn_ElapsedTime(from_time,to_time) as Elapsed_Time
      From #Tmp
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    8

    Smile

    it works. Thanks mate
    elam

Posting Permissions

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