declare @t table(rec_times varchar(20))
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'update @t set rec_times=stuff(rec_times,1,3,'')+(case left(rec_times,2) when '上午' then ' AM' else ' PM' end)select * from @t/*
rec_times
--------------------
09:22:44 AM
08:22:44 AM
12:41:20 PM
13:41:37 PM
14:22:44 PM
*/
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'update @t set rec_times=stuff(rec_times,1,3,'')+(case left(rec_times,2) when '上午' then ' AM' else ' PM' end)select * from @t/*
rec_times
--------------------
09:22:44 AM
08:22:44 AM
12:41:20 PM
13:41:37 PM
14:22:44 PM
*/
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'update @t set rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
else
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' PM' else substring(rec_times,4,8)+' PM' end)
end)select * from @t
--写的太繁琐了
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'update @t set rec_times=stuff(rec_times,1,3,'')+(case left(rec_times,2) when '上午' then ' AM' else ' PM' end)update @t set rec_times = case when left(rec_times,1) = '0'
then right(rec_times,10) else rec_times end
select * from @t
rec_times
--------------------
9:22:44 AM
8:22:44 AM
12:41:20 PM
13:41:37 PM
14:22:44 PM
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'
insert into @t select '上午 04:22:44'
insert into @t select '上午 04:22:44'update @t set rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
else
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' PM' else substring(rec_times,4,8)+' PM' end)
end)select * from @t/*
rec_times
--------------------
9:22:44 AM
8:22:44 AM
12:41:20 PM
13:41:37 PM
14:22:44 PM
4:22:44 AM
4:22:44 AM
*/
--0已经去掉了
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'
insert into @t select '上午 04:22:44'
insert into @t select '上午 04:22:44'update @t set rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
when left(rec_times,2)='下午' then substring(rec_times,4,8)+' PM' end)select * from @t
--这个意思吗?
不是,我是指如下
update @t set rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
else (這裏加個判斷如果是 ='下午' 才執行後面的語句)
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' PM' else substring(rec_times,4,8)+' PM' end)
end)也就是說要判斷原記錄有 上午或下午 才轉成我想要的結果
insert into @t select '上午 09:22:44'
insert into @t select '上午 08:22:44'
insert into @t select '下午 12:41:20'
insert into @t select '下午 13:41:37'
insert into @t select '下午 14:22:44'
insert into @t select '上午 04:22:44'
insert into @t select '上午 04:22:44'update @t set
rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
else
(case when left(rec_times,2)='下午' then substring(rec_times,4,8)+' PM' end) end)select * from @t
--这样?
是這樣的
update @t set rec_times=ltrim(case when left(rec_times,2)='上午' then
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' AM' else substring(rec_times,4,8)+' AM' end)
如上這段是正確 (到這裏加個判斷如果是 ='下午' 才執行else後面的語句)
else
(case when substring(rec_times,4,1)='0' then substring(rec_times,5,8)+' PM' else substring(rec_times,4,8)+' PM' end)
end)因的數據庫真正的原記錄情況如下(記錄前有個空格的)
8:41:37 PM
上午 09:22:44
上午 08:22:44
下午 12:41:20
下午 13:41:37
下午 14:22:44
16:22:44 AM我要的結果如下(要求記錄前有個空格的)
8:41:37 PM
9:22:44 AM
8:22:44 AM
12:41:20 PM
13:41:37 PM
14:22:44 PM
16:22:44 AM
insert into @t select ' 上午 09:22:44'
insert into @t select ' 上午 08:22:44'
insert into @t select ' 下午 12:41:20'
insert into @t select ' 下午 13:41:37'
insert into @t select ' 下午 14:22:44'
insert into @t select ' 上午 04:22:44'
insert into @t select ' 上午 04:22:44'
insert into @t select ' 8:41:37 PM'
insert into @t select ' 16:22:44 AM'select * from @tupdate @t set
rec_times=case when left(ltrim(rec_times),2)='上午' then
' '+(case when substring(ltrim(rec_times),4,1)='0' then substring(ltrim(rec_times),5,8)+' AM' else substring(ltrim(rec_times),4,8)+' AM' end)
when left(ltrim(rec_times),2)='下午' then ' '+substring(ltrim(rec_times),4,8)+' PM'
else rec_times end
select * from @t
insert into @t select ' 上午 09:22:44'
insert into @t select ' 上午 08:22:44'
insert into @t select ' 下午 12:41:20'
insert into @t select ' 下午 13:41:37'
insert into @t select ' 下午 14:22:44'
insert into @t select ' 上午 04:22:44'
insert into @t select ' 上午 04:22:44'
insert into @t select ' 8:41:37 PM'
insert into @t select ' 16:22:44 PM'
insert into @t select ' 下午 04:41:37'--select * from @tupdate @t set
rec_times=case when left(ltrim(rec_times),2)='上午' then
' '+(case when substring(ltrim(rec_times),4,1)='0' then substring(ltrim(rec_times),5,8)+' AM' else substring(ltrim(rec_times),4,8)+' AM' end)
when left(ltrim(rec_times),2)='下午' then
' '+(case when substring(ltrim(rec_times),4,1)='0' then substring(ltrim(rec_times),5,8)+' PM' else substring(ltrim(rec_times),4,8)+' PM' end)
else rec_times end
select * from @t
--这样应该可以了