人气不行,换个地方看看--一case语句问题
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 '
SELECT case
when convert(int,substring(dt,6,2))=2 and (convert(int,substring(dt,9,2))=28 or convert(int,substring(dt,9,2))=29 ) and ((convert(int,substring(dt,1,4))%100+convert(int,substring(dt,1,4))%4)> =1 or convert(int,substring(dt,1,4))%400=0)
then stuff(dt,9,2, '28 ')
when (convert(int,substring(dt,6,2))=7 or convert(int,substring(dt,6,2))=8 or
convert(int,substring(dt,6,2))=10 or convert(int,substring(dt,6,2))=11 or
convert(int,substring(dt,6,2))=1 or convert(int,substring(dt,6,2))=3 or
convert(int,substring(dt,6,2))=5 )
and convert(int,substring(dt,9,2))> 31
then stuff(dt,9,2, '31 ')
when
(convert(int,substring(dt,6,2))=2 or convert(int,substring(dt,6,2))=4 or
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 ')
where len(dt)> 10
and cast(substring(dt,12,2) as int) <1
update @t
set dt=stuff(dt,12,2, '23 ')
where len(dt)> 10
and cast(substring(dt,12,2) as int) > 23
--分
update @t
set dt=stuff(dt,15,2, '01 ')
where len(dt)> 10
and cast(substring(dt,15,2) as int) <1
update @t
set dt=stuff(dt,15,2, '59 ')
where len(dt)> 10
and cast(substring(dt,15,2) as int) > 59
--秒
update @t
set dt=stuff(dt,18,2, '01 ')
where len(dt)> 10
and cast(substring(dt,18,2) as int) <1
update @t
set dt=stuff(dt,18,2, '59 ')
where len(dt)> 10
and cast(substring(dt,18,2) as int) > 59
select * from @t
--结果
dt
------------------------------
2006-02-28 13:01:00.000
2005-03-31 13:01:00.000
2004-02-29 13:01:00.000
2003-12-31 13:59:00.000
2004-06-30 13:59:00.000
2000-02-28 13:01:77.000
2002-03-30
（所影响的行数为 7 行）
[解决办法]
有错误，改下
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, '23 ')
where len(dt)> 10
and cast(substring(dt,12,2) as int) > 23
--分
update @t
set dt=stuff(dt,15,2, '59 ')
where len(dt)> 10
and cast(substring(dt,15,2) as int) > 59
--秒
update @t
set dt=stuff(dt,18,2, '59 ')
where len(dt)> 10
and cast(substring(dt,18,2) as int) > 59
select * from @t
--结果
dt
------------------------------
2006-02-28 13:00:00.000
2005-03-31 13:00:00.000
2004-02-29 13:00:00.000
2003-12-31 13:59:00.000
2004-06-30 13:59:00.000
2000-02-28 13:00:59.000
2002-03-30
（所影响的行数为 7 行）
