time difference HELP!!

Question

time difference HELP!!

bass8117

SSChasing Mays

Points: 649
More actions
December 7, 2009 at 9:13 am

#137412

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

bass8117 SSChasing Mays Points: 649 More actions · Answer 2

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

bass8117 SSChasing Mays Points: 649 More actions · Answer 3

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.

Pavel Pawlowski Ten Centuries Points: 1244 More actions · Answer 4

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