declare @t varchar(100) set @t='20081001183949' select dateadd(ss,cast(substring(@t,13,2) as int), dateadd(mi,cast(substring(@t,11,2) as int), dateadd(hh,cast(substring(@t,9,2) as int), cast(left(@t,8) as datetime))) )
DECLARE @DATE VARCHAR(20) SET @DATE='20081001183949'SELECT CONVERT(VARCHAR(10),CAST(LEFT(@DATE,8) AS DATETIME),120)+' '+SUBSTRING(@DATE,11,2)+':'+SUBSTRING(@DATE,13,2)+':'+RIGHT(@DATE,2)
用convert可以吗?要做的操作是update dbo.check_in set tstate = '成功' , result = transtime from check_in join tb1 on shmobile = inputtel 现在呢,transtime字段是文本型的,而result是时间型的,update的时候会出错。
update dbo.check_in set tstate = '成功' , result = cast(substring(transtime,1,4) + '-' + substring(transtime,5,2)+ '-' + substring(transtime,7,2)+ ' '+substring(transtime,9,2) + ':' + substring(transtime,11,2) + ':'+ substring(transtime,13,2) as datetime) from check_in join tb1 on shmobile = inputtel
set @t = '20081001183949'select substring(@t,1,4) + '-' + substring(@t,5,2)+ '-' + substring(@t,7,2)
+ ' ' + substring(@t,9,2) + ':' + substring(@t,11,2) + ':'+ substring(@t,13,2)
结果:2008-10-01 18:39:49
declare @dt as varchar(20)
set @dt = '20081001183949'
select dt = left(@dt , 4) + '-' + substring(@dt , 5 , 2) + '-' + substring(@dt , 7 , 2) + ' ' + substring(@dt , 9 , 2) + ':' + substring(@dt , 11 , 2) + ':' + right(@dt , 2)/*
dt
---------------------------------
2008-10-01 18:39:49(所影响的行数为 1 行)
*/
set @t='20081001183949' select
dateadd(ss,cast(substring(@t,13,2) as int),
dateadd(mi,cast(substring(@t,11,2) as int),
dateadd(hh,cast(substring(@t,9,2) as int),
cast(left(@t,8) as datetime)))
)
SET @DATE='20081001183949'SELECT CONVERT(VARCHAR(10),CAST(LEFT(@DATE,8) AS DATETIME),120)+' '+SUBSTRING(@DATE,11,2)+':'+SUBSTRING(@DATE,13,2)+':'+RIGHT(@DATE,2)
declare @t varchar(50)
set @t = '20081001183949'select convert(datetime,substring(@t,1,8) + ' ' +
substring(@t,9,2) + ':' + substring(@t,11,2) + ':' + substring(@t,13,2))-----------------------
2008-10-01 18:39:49.000(1 行受影响)
update dbo.check_in
set
tstate = '成功' ,
result = cast(substring(transtime,1,4) + '-' + substring(transtime,5,2)+ '-' + substring(transtime,7,2)+ ' '+substring(transtime,9,2) + ':' + substring(transtime,11,2) + ':'+ substring(transtime,13,2) as datetime)
from check_in
join tb1
on shmobile = inputtel