Results 1 to 5 of 5

Thread: datetime

  1. #1
    Join Date
    May 2004
    Posts
    36

    Unanswered: datetime

    i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.

    how do i do this in sql server??

    thanks in advance
    e3witt

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    select cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
    +cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    SET ANSI_NULLS OFF
    SET NOCOUNT ON
    GO

    if object_id(N'dbo.fn_ElapsedTime') is not null begin
    drop function dbo.fn_ElapsedTime
    print 'Function dbo.fn_ElapsedTime dropped'
    end
    go

    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

    if object_id(N'dbo.fn_ElapsedTime') is not null begin
    print 'Function dbo.fn_ElapsedTime created'
    end
    go
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    May 2004
    Posts
    36
    ok... now it's working just the way i was wanting.

    thank you.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't forget about the modulo operator (%). It's hand for converting time values:

    select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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