none
SQL Elapsed Time Query

    Question

  • I have a query that I'm building to return the elapsed time the record has been sitting in the table.  The table contains lots of records and I would like to get the unique elapsed time for each record in the table.  The below query works well when I have only 1 record but what if I have lots of records?  In that case it returns the same elapsed time for all records.  How can I get it to return the unique elapsed time for each records?  Any help is greatly appreciated!

    declare @datetime1 datetime
    declare @datetime2 datetime
    declare @date_only datetime
    declare @time_only datetime
    declare @date_plus_time datetime
    declare @elapsed_time datetime
    declare @elapsed_days int
    declare @elapsed_hours int
    declare @elapsed_minutes int
    declare @elapsed_seconds int
    declare @elapsed_milliseconds int
    declare @Sales_Doc_Num varchar (21)

    -- Load 2 datetime values
    select
    @datetime1 = dex_row_ts,
    @Sales_Doc_Num = sopnumbe
     from SOP10100Confirmed
     group by DEX_ROW_TS, SOPNUMBE
     
    select @datetime2 = (select GETUTCDATE())


    -- Get date only from datetime using DATEAADD/DATEDIFF functions
    select @date_only = dateadd(day,datediff(day,0,@datetime1),0)

    -- Get time only from datetime by subtracting date only
    select @time_only = @datetime2-dateadd(day,datediff(day,0,@datetime2),0)

    -- Add date only and time only together
    select @date_plus_time = @date_only+@time_only

    -- Get elapsed time as the difference between 2 datetimes
    select @elapsed_time = @datetime2-@datetime1

    -- Get elapsed time parts as time since 1900-01-01 00:00:00.000
    select @elapsed_days = datediff(day,0,@elapsed_time)
    select @elapsed_hours = datepart(hour,@elapsed_time)
    select @elapsed_minutes = datepart(minute,@elapsed_time)
    select @elapsed_seconds = datepart(second,@elapsed_time)
    select @elapsed_milliseconds = datepart(millisecond,@elapsed_time)

    declare @cr varchar(4), @cr2 varchar(4)
    select @cr = char(13)+Char(10)
    select @cr2 = @cr+@cr

    select(
     convert(varchar(3),@elapsed_days)+' Days '+convert(varchar(3),@elapsed_hours)+' Hrs '+
     convert(varchar(3),@elapsed_minutes)+' Mins '+convert(varchar(3),@elapsed_seconds)+' Sec '
     +convert(varchar(3), @elapsed_milliseconds)+' Mls ') as Batch_Time, sopnumbe
     from SOP10100Confirmed
     group by SOPNUMBE

     

     

    Wednesday, June 16, 2010 10:16 PM

Answers

  • Uri,

    I figured it out, but thanks for the offer.  I decided not to declare the variable and simply coded each of the calculations.  All is working well now.

     

    Wally

    • Marked as answer by LekssEditor Sunday, June 20, 2010 10:53 PM
    Friday, June 18, 2010 3:38 PM

All replies

  • Can you post sample data for testing? BTW what version are you using?
    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 17, 2010 5:56 AM
  • Can you post sample data for testing? BTW what version are you using?
    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 17, 2010 5:56 AM
  • Uri,

    I figured it out, but thanks for the offer.  I decided not to declare the variable and simply coded each of the calculations.  All is working well now.

     

    Wally

    • Marked as answer by LekssEditor Sunday, June 20, 2010 10:53 PM
    Friday, June 18, 2010 3:38 PM
  • Searching for a solution to this issue I've come across a lot of overly verbose code and so decided to roll my own:

    declare @sd datetime, @ed datetime
    set @sd = '2012-12-29 02:11:17.840'
    set @ed = '2012-01-01 20:34:34.110'
    
    --Format days/hrs/mins/secs
    SELECT CAST(DATEDIFF(d, @sd, @ed) AS varchar) + ':' + CONVERT(varchar, @ed - @sd, 08)
    --Format days/hrs/mins/secs/ms
    SELECT CAST(DATEDIFF(d, @sd, @ed) AS varchar) + ':' + CONVERT(varchar, @ed - @sd, 14)

    Hope this helps.


    • Edited by mamo Friday, January 06, 2012 4:13 PM
    Friday, January 06, 2012 4:06 PM