declare @t table(rec_times varchar(20)) insert into @t select '下午 02:41:20' union all select '下午 02:41:37' union all select '下午 04:22:44'select case when left(rec_times,4)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end as rec_times from @t
select substring('下午 02:41:20',4,8)+ substring(cast(convert(datetime,substring('下午 02:41:20',4,8),131) as varchar(30)),17,2) from 表select substring(rec_times,4,8)+ substring(cast(convert(datetime,substring(rec_times,4,8),131) as varchar(30)),17,2) from 表
declare @t table(rec_times varchar(20)) insert into @t select '下午 02:41:20' union all select '上午 02:41:37' union all select '上午 04:22:44'select case when left(rec_times,2)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end as rec_times from @t --写错了一个数
用一条 Update 语句直接修改即可: Update AA Set rec_times=Right(rec_times,8)+Case When left(rec_times,2)='下午' Then ' PM' Else ' AM' End
不好意思,我要的是UPDATE语句,再麻烦大家一下
declare @t table(rec_times varchar(20)) insert into @t select '下午 02:41:20' union all select '上午 02:41:37' union all select '上午 04:22:44'update @t set rec_times=case when left(rec_times,2)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end select * from @t
还可以用convert()为PM格式吧
Update AA Set rec_times=Right(rec_times,8)+Case When left(rec_times,2)='下午' Then ' PM' Else ' AM' End 这样不就可以么??
insert into @t select '下午 02:41:20'
union all select '下午 02:41:37'
union all select '下午 04:22:44'select case when left(rec_times,4)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end as rec_times from @t
from 表select substring(rec_times,4,8)+ substring(cast(convert(datetime,substring(rec_times,4,8),131) as varchar(30)),17,2)
from 表
insert into @t select '下午 02:41:20'
union all select '上午 02:41:37'
union all select '上午 04:22:44'select case when left(rec_times,2)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end as rec_times from @t
--写错了一个数
Update AA Set rec_times=Right(rec_times,8)+Case When left(rec_times,2)='下午' Then ' PM' Else ' AM' End
insert into @t select '下午 02:41:20'
union all select '上午 02:41:37'
union all select '上午 04:22:44'update @t set rec_times=case when left(rec_times,2)='上午' then substring(rec_times,4,8)+' AM' else substring(rec_times,4,8)+' PM' end
select * from @t
这样不就可以么??