How to calculate exact time between two time?

Atul Patil

9y989

date_time reader_name vehicle_name

2017-04-14 00:01:00.000 RCC Entry TRANSDEV 502

2017-04-14 00:03:00.000 RCC Exit TRANSDEV 502

I want to know the exact time between reader_name of vehicle

Result like this:-

vehicle_name from to time

TRANSDEV 502 RCC Entry RCC Exit 2 hours

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rathrola Prem Kumar9y ago
Here is a sample for you

declare @Date01 datetime= '01-SEP-2015 22:21:00', @Date02 datetime= '02-SEP-2015 01:28:00'
select DATEDIFF(SECOND,@Date01 ,@Date02 )*1.00 / (60*60)

Output : 3.1166 hours

+1
Manish Kumar9y ago
Although Nilesh has given a good solution for your requirement.You can also try below query.

declare @tbl table
(date_time datetime,
reader_name varchar(100),
name varchar(100))
insert into @tbl
select '2017-04-14 01:01:00.000','RCC Entry','TRANSDEV 502'
union all
select '2017-04-14 03:03:00.000','RCC Exit','TRANSDEV 502'
select t.name,t.[from],t1.[to],
DATEDIFF(HOUR, mintime,maxtime) as differecinHour
from (
select tbl.name,reader_name as [from],mintime from (
select name,min(date_time) as mintime from @tbl
group by name
) tbl inner join @tbl b on tbl.name=b.name and tbl.mintime=b.date_time
) t
inner join (
select tbl1.name,reader_name as [to],maxtime from (
select name,max(date_time) as maxtime from @tbl
group by name
) tbl1 inner join @tbl c on tbl1.name=c.name and tbl1.maxtime=c.date_time
)t1 on t.name=t1.name
Thanks,
+1
Nilesh Shah9y ago
Hi,

I noted that your time diff. is in minutes and not in hours so I am showing answer in minutes, if you want to change it check MSDN for DateDiff function

Also, I suggest you next time also post table creation script and table insert data scripts so that someone trying query does not have to do it all.

anyways, I created table and inserted data, and here is your script
select
    VE.vehicle_name,
    VE.reader_name as 'from',
    VX.reader_name as 'to',
    DATEDIFF(MI, VE.date_time, VX.date_time) as 'time (in minutes)'
from
    VehicleTracking VE
    inner join VehicleTracking VX on VE.vehicle_name = VX.vehicle_name
where
    VE.reader_name = 'RCC Entry'
    and VX.reader_name = 'RCC Exit'
There are some suggestions though - make the two columns vehicle_name and reader_name a foreign key and move the values to a seperate table. also there should be a PK in this table, may be auto id integer. and there should be a tracking id so as to identify the relavent "RCC Exit" record for "RCC Entry"
+3