How to calculate exact time between two time?

Question

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

Nilesh Shah · Answer

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

Manish Kumar · Answer

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.namet.[from]t1.[to] DATEDIFF(HOUR mintimemaxtime) as differecinHour from ( select tbl.namereader_name as [from]mintime from ( select namemin(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.namereader_name as [to]maxtime from ( select namemax(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

Rathrola Prem Kumar · Answer

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