# 人气不行,换个地方看看-一case语句有关问题(2)

www.MyException.Cn  网友分享于：2013-01-17  浏览：6次

convert(int,substring(dt,6,2))=6     or     convert(int,substring(dt,6,2))=9     or
convert(int,substring(dt,6,2))=12     )
and         convert(int,substring(dt,9,2))> =31
then         stuff(dt,9,2, '30 ')
when         convert(int,substring(dt,6,2))> 12
then         stuff(dt,6,2, '12 ')
when         convert(int,substring(dt,12,2))> 24
then         stuff(dt,12,2, '24 ')
when         convert(int,substring(dt,15,2))> 59
then         stuff(dt,15,2, '59 ')
when         convert(int,substring(dt,18,2))> 59
then         stuff(dt,18,2, '59 ')

else
dt
end
FROM     @t
order     by     dt

------解决方案--------------------

declare @t table(dt varchar(30))
insert @t
select '2006-02-28 13:00:00.000 ' union all
select '2005-03-32 13:00:00.000 ' union all
select '2004-02-30 13:00:00.000 ' union all
select '2003-17-36 13:70:00.000 ' union all
select '2004-06-31 13:60:00.000 ' union all
select '2000-02-28 13:00:77.000 ' union all
select '2002-03-30 '

--月份
update @t
set dt=stuff(dt,6,2, '01 ')
where cast(substring(dt,6,2) as int) <1

update @t
set dt=stuff(dt,6,2, '12 ')
where cast(substring(dt,6,2) as int) > 12

--日
while exists (select 1 from @t where isdate(substring(dt,1,10)) <> 1)
update @t
set dt=stuff(dt,9,2,cast(cast(substring(dt,9,2) as int)-1 as varchar))
where isdate(substring(dt,1,10)) <> 1

--时
update @t
set dt=stuff(dt,12,2, '01 ')