求一个SQL 语句（关于改变数据）

[pennymay]

一个table里有一个字段,之前是000020071231格式,就是前4位是0-9999的数,后8位以年月日的格式出现,
现在要改成200712310000的格式,
要求是如果开始是120071231,要变成200712310001,如果是9920071231,变成200712310099,就是之前不满4位的都要变成4位,

谢谢大家!

[wzjpsq]

updata table set a=left(a,8)*10000+rigt(a,length(a)-8)

[pennymay]

[Quote=引用 1 楼 wzjpsq 的回复:]
updata table set a=left(a,8)*10000+rigt(a,length(a)-8)
[/Quote]

有错误

[wzjpsq]

updata table set a=left(a,8)*10000+right(a,length(a)-8)
写错了

[pennymay]

还是错误

Msg 195, Level 15, State 10, Line 1
'length' is not a recognized built-in function name.

[wzjpsq]

把length 写成len

[pennymay]

新的错误
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

[tianhuo_soft]

create table #t080715(test varchar(12))
insert into #t080715
select '000020071231' union all
select '120071231' union all
select '9920071231' 
select * from #t080715

update #t080715
set test=(case len(test) when 12 then right(test,8)+left(test,4)
        when 11 then right(test,8)+'0'+left(test,3)
        when 10 then right(test,8)+'00'+left(test,2)
        when 9 then right(test,8)+'000'+left(test,1) 
        else right(test,8)+'0000' end 
          )

[mskyp]

select right(date1,8)+right(10000+substring(date1,1,len(date1)-8),4)
from taba

date1表列名

[hery2002]

declare  @tb table(test varchar(12))
insert into @tb
select '000020071231' union all
select '120071231' union all
select '9920071231' 


select test,
       right(test,8)+ right(10000 + cast(left(test,case when len(test)>8 then len(test)-8 end) as int),4) as newVal
from @tb
/*
test         newVal
------------ --------------------
000020071231 200712310000
120071231    200712310001
9920071231   200712310099
*/