我有下面的表NTESQ004HDR
runid TrainNumber divcode ScheduledDT schedarrtime scheddepdate -- needed format of schedarrtime
a 16127 NYY 2006 2241 2242 --20/06/2018 22:41:00.00
a 16127 TVC 2006 2315 2320
a 16127 CRY 2006 2341 2342
a 16127 KVU 2006 2349 2350
a 16127 VAK 2006 0000 0000 --21/06/2018 00:00:00.00
a 16127 PVU 2006 0015 0016
a 16127 QLN 2006 0030 0035 --21/06/2018 00:30:00.00
a 16127 KYJ 2006 0118 0120
a 16127 HAD 2006 0135 0136
a 16127 AMPA 2006 0150 0151
a 16128 TVC 2006 2315 2320
a 16128 CRY 2006 2341 2342
a 16128 KVU 2006 2349 2350
a 16128 PVU 2006 0015 0016
a 16128 QLN 2006 0030 0035
a 16128 KYJ 2006 0118 0120
a 16128 HAD 2006 0135 0136
a 16128 AMPA 2006 0150 0151
上面写着,16127
列车到达NYY
站的预定时间是20/06/2018 22:41:00.00
,出发时间是20/06/2018 22:42:00.00
。这些时间由(我尝试的内容...)返回
declare @year varchar(10) = (SELECT YEAR(GETDATE()))
select RUNID, divcode
, TRY_CONVERT(datetime, right(ScheduledDT,2)+'-'+left(ScheduledDT,2)+'-'+@year+' '+left(scheddepdate,2)+':'+RIGHT(scheddepdate,2)+':00.00') 'schdeptime'
, TRY_CONVERT(datetime, right(ScheduledDT,2)+'-'+left(ScheduledDT,2)+'-'+@year+' '+left(schedarrtime,2)+':'+RIGHT(schedarrtime,2)+':00.00') 'scharrtime'
, TrainNumber
from NTESQ004HDR
其中,as返回20/06/2018 00:30:00.00
作为QLN
站点的schedarrtime
。但我需要的是21/06/2018 00:30:00.00
。
我的预期输出:
runid TrainNumber divcode schedarrtime scheddepdate
a 16127 NYY 20/06/2018 22:41:00.00 20/06/2018 22:42:00.00
a 16127 TVC 20/06/2018 23:15:00.00 20/06/2018 23:20:00.00
a 16127 CRY 20/06/2018 23:41:00.00 20/06/2018 23:42:00.00
a 16127 KVU 20/06/2018 23:49:00.00 20/06/2018 23:50:00.00
a 16127 VAK 21/06/2018 00:00:00.00 21/06/2018 00:00:00.00 |here my table gets changes
a 16127 PVU 21/06/2018 00:15:00.00 21/06/2018 00:16:00.00 |here my table gets changes
a 16127 QLN 21/06/2018 00:30:00.00 21/06/2018 00:35:00.00 |here my table gets changes
a 16127 KYJ 21/06/2018 01:18:00.00 21/06/2018 01:20:00.00 |here my table gets changes
a 16127 HAD 21/06/2018 01:35:00.00 21/06/2018 01:36:00.00 |here my table gets changes
a 16127 AMPA 21/06/2018 01:50:00.00 21/06/2018 01:51:00.00 |here my table gets changes
a 16128 TVC 20/06/2018 23:15:00.00 20/06/2018 23:20:00.00
a 16128 CRY 20/06/2018 23:41:00.00 20/06/2018 23:42:00.00
a 16128 KVU 20/06/2018 23:49:00.00 20/06/2018 23:50:00.00
a 16128 PVU 21/06/2018 00:15:00.00 21/06/2018 00:16:00.00 |here my table gets changes
a 16128 QLN 21/06/2018 00:30:00.00 21/06/2018 00:35:00.00 |here my table gets changes
a 16128 KYJ 21/06/2018 01:18:00.00 21/06/2018 01:20:00.00 |here my table gets changes
a 16128 HAD 21/06/2018 01:35:00.00 21/06/2018 01:36:00.00 |here my table gets changes
a 16128 AMPA 21/06/2018 01:50:00.00 21/06/2018 01:51:00.00 |here my table gets changes
便笺
SQL server是唯一生成的,每5个mintues.
我将查找当前车站的下一个和前一个主要车站。(这取决于我期望的表)。
更新1:列车16127到达20/06/2018 23:49:00.00
的KVU
站。几分钟后,它到达了21/06/2018 00:00:00.00
的VAK
。但是20/06/2018
的日期仍然保留在ScheduledDT
上。在我需要的地方,当schedarrtime
穿过2359
时添加一个日期。
更新2:
在这种情况下,我的前端团队(Web开发人员),解决这个问题。当第二天旅行时,他们通过评估列车的当前时间,将ScheduledDT
列插入为next date
。所以我在ScheduledDT
列中有正确日期,如下所示,
runid TrainNumber divcode ScheduledDT schedarrtime scheddepdate
b 6004 TEN 2106 1530 1635
b 6004 CVP 2106 1637 1700
b 6004 SRT 2106 1702 1730
b 6004 VPT 2106 1732 1830
b 6004 MDU 2106 1835 1837
b 6004 KQN 2106 1902 1925
b 6004 DG 2206 0030 0035 --here date is 2206 as well as time is 0030
b 6004 MDU 2206 0240 0319
b 6004 VPT 2206 0320 0327
谢谢大家
发布于 2018-06-21 04:48:50
因为所有的列都是varchar
,所以你可以直接concatenate
它。不需要TRY_CONVERT
。日期在SQL Server中没有格式...因为您希望以这种格式返回它,所以它必须是一个varchar
。
--load up your sample data
with cte as(
select
runid = 'a'
,TrainNumber = '16127'
,divcode = 'NYY'
,ScheduleDT = '2006'
,schedarrtime = '2241'
,sheddeptdate = '2242'
)
--here's the formula. Repeat this with sheddeptdate.
select *
,new_schedarrtime = left(ScheduleDT,2) + '/' + right (ScheduleDT,2) + '/' + cast(datepart(year,getdate()) as char(4) ) + ' ' + left(schedarrtime,2) + ':' + right(schedarrtime,2) + ':' + '00.00'
from cte
因此,您需要两行代码:
,new_schedarrtime = left(ScheduleDT,2) + '/' + right (ScheduleDT,2) + '/' + cast(datepart(year,getdate()) as char(4) ) + ' ' + left(schedarrtime,2) + ':' + right(schedarrtime,2) + ':' + '00.00'
,new_scheddeptdate = left(ScheduleDT,2) + '/' + right (ScheduleDT,2) + '/' + cast(datepart(year,getdate()) as char(4) ) + ' ' + left(sheddeptdate,2) + ':' + right(sheddeptdate,2) + ':' + '00.00'
https://stackoverflow.com/questions/50956691
复制相似问题