time difference HELP!!

  • Hello all I am coding a report and i need some help. I hope i can explain this right. i have pulled all the info i need but i need to get the time difference between 2 lines. for instance

    A: Start Time 2009-04-21 04:46:00.0000 Stop Time2009-04-21 04:49:00.000

    B: Start Time 2009-04-21 05:36:00.000 Stop Time 2009-04-21 05:39:00.000

    I need to get the time difference between the stop time on line A to the Start time on line B in hours and mins. any clue how to do this? i can get the datediff between start and stop times on the same line but not different lines. so any help would be great. thanks

  • you'll want to use the datediff function; but would you want a value in minutes if the difference was say 1823 minutes, or would you rather see 30 hours and 23 minutes?

    SELECT DATEDIFF(minute,@d1,@d2) AS Minutes

    SELECT DATEDIFF(minute,@d1,@d2)/60 AS Hours,DATEDIFF(minute,@d1,@d2)%60 AS Minutes

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • i see what you are saying. I would like to see hours and mins. so should i try your second answer? thanks Lowell

  • the post has been updated for more clarity. please let me know if this can be done. Lowell yours worked great for the same line but not 2 different lines.

  • I think, you want calculate a time difference between current and previous rows in a table witch is representing eg. some type of log.

    If this is what you intend, you can try something like this:

    DECLARE @times TABLE (

    ID int,

    StartTime datetime,

    EndTime datetime

    )

    INSERT INTO @times (

    ID,

    StartTime,

    EndTime

    )

    SELECT 1 AS ID, '2009-04-21 04:46:00.000' As StartTime, '2009-04-21 04:49:00.000' As StopTime UNION ALL

    SELECT 2 AS ID, '2009-04-21 05:36:00.000' As StartTime, '2009-04-21 05:39:00.000' As StopTime UNION ALL

    SELECT 3 AS ID, '2009-04-21 05:40:00.000' As StartTime, '2009-04-21 06:39:00.000' As StopTime

    ;WITH PreviousTimes AS (

    SELECT

    (ROW_NUMBER() OVER(ORDER BY EndTime))+1 Row,

    StartTime AS PreviousStart,

    EndTime AS PreviousEnd

    FROM @times

    ),

    CurrentTimes AS(

    SELECT

    ROW_NUMBER() OVER(ORDER BY EndTime) Row,

    StartTime AS CurrentStart,

    EndTime AS CurrentEnd

    FROM @times

    )

    SELECT

    P.Row,

    P.PreviousStart,

    P.PreviousEnd,

    C.CurrentStart,

    C.CurrentEnd,

    C.CurrentStart - P.PreviousEnd AS Difference

    FROM PreviousTimes P

    INNER JOIN CurrentTimes C ON P.Row = C.Row

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply