SELECT distinct
a.[IPAddress]
,CONVERT(VARCHAR(20),a.[GPStime],108)as starttime
,CONVERT(VARCHAR(20),b.[GPStime],108)as endtime
,DATEDIFF (s, a.[GPStime] , b.[GPStime] ) as time
from (SELECT * FROM [GPS数据分析].[dbo].[20140927实时数据]
where [IPAddress] in (SELECT [车辆IP] FROM [GPS数据分析].[dbo].[0车辆信息] where 线路号='181')
and ([StationNumber]=22 and [InOrOutFlag]=1)
) as a
inner join
(SELECT * FROM [GPS数据分析].[dbo].[20140927实时数据]
where [IPAddress] in (SELECT [车辆IP] FROM [GPS数据分析].[dbo].[0车辆信息] where 线路号='181')
and ([StationNumber]=23 and [InOrOutFlag]=0)
)
as b
on a.[IPAddress]= b.[IPAddress] and DATEDIFF (s, a.[GPStime] , b.[GPStime] ) BETWEEN 0 AND 600
order by endtime asc
这是我的程序,最后出来的时间结果没有按照我想要的时分秒转化,还是原来的年月日时分秒,是不是我函数用错了?
a.[IPAddress]
,CONVERT(VARCHAR(20),a.[GPStime],108)as starttime
,CONVERT(VARCHAR(20),b.[GPStime],108)as endtime
,DATEDIFF (s, a.[GPStime] , b.[GPStime] ) as time
from (SELECT * FROM [GPS数据分析].[dbo].[20140927实时数据]
where [IPAddress] in (SELECT [车辆IP] FROM [GPS数据分析].[dbo].[0车辆信息] where 线路号='181')
and ([StationNumber]=22 and [InOrOutFlag]=1)
) as a
inner join
(SELECT * FROM [GPS数据分析].[dbo].[20140927实时数据]
where [IPAddress] in (SELECT [车辆IP] FROM [GPS数据分析].[dbo].[0车辆信息] where 线路号='181')
and ([StationNumber]=23 and [InOrOutFlag]=0)
)
as b
on a.[IPAddress]= b.[IPAddress] and DATEDIFF (s, a.[GPStime] , b.[GPStime] ) BETWEEN 0 AND 600
order by endtime asc
这是我的程序,最后出来的时间结果没有按照我想要的时分秒转化,还是原来的年月日时分秒,是不是我函数用错了?