asked on

How to get Date Difference between Two Dates

Hi Experts,
We have table having following column

ID,systime,eventcode
ID = field autonumber
Systime =System Time
EventCode = 4,12 ( where 4= Start and 12= stop )

we need to calculate Vehicle working duration between two dates.

Kindly check the attached picture file.
Snap1.png

Aneesh

use datediff function

http://technet.microsoft.com/en-us/library/ms189794.aspx

select datediff (minutes, startDate, endDate )

select datediff (minutes, (select sysdate from yourTable where eventID = 4 ) , (select sysdate from yourTable where eventID = 12 ) )

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

could be a big assumption

The information appears to be collected 24x7 or 24x5, so for ANY selection made involving a start point, a start event could be disassociated from its related stop event or vice-versa.

in fact row 1 of the image displays exactly that (an eventCode 12)

In that image you have asked for data >= '2013-09-10 00:00:00'
and the first row has a datetime of '2013-09-10 21:44:43:000'
>> does that mean the equipment was running between 00:00:00 and 21:44:43?

---------
whilst you have specified MS SQL 2008 as a topic, do you happen to be using SQL 2012?
{SQL 2012 has a feature that would be darn handy: lead()/lag() }

PortletPaul

any chance of running this and providing the results as text please?

SELECT
        ID
      , devicenum
      , systime
      , eventcode
FROM gpssystem.dbo.gpshistory
WHERE devicenum = '359231039842834'
        AND eventcode IN (4, 12)
        AND systime >=   '2013-09-09 00:00:00'
        AND systime  <   '2013-09-11 00:00:00'

Open in new window