[color=darkred]declare @name varchar(20),@sid varchar(30)
declare @y varchar(8),@m varchar(8),@d varchar(8)
declare cur cursor
for select 姓名,身份证号,substring(身份证号,7,2),substring(身份证号,9,2),substring(身份证号,11,2) from Sheet1$
open cur
fetch next from cur into @name,@sid,@y,@m,@d
while @@fetch_status = 0
begin
if len(@sid)=15
begin
--print 'update [192.168.123.201].htbz.dbo.t_Worker set year(wk_birthday) = '+substring(@sid,7,4)+',month(wk_birthday) = '+substring(@sid,11,2)+',day(wk_birthday) = '+substring(@sid,13,2)+' where wk_name = '+@name+''
--select replace(year(wk_birthday),@str2 = year(wk_birthday),@y), @str3= replace(month(wk_birthday),month(wk_birthday),@m) ,replace(day(wk_birthday),day(wk_birthday),@d) from [192.168.123.201].htbz.dbo.t_Worker where wk_name = @name
print @y+'-'+@m+'-'+@d
update [192.168.123.201].htbz.dbo.t_Worker set wk_birthday = '19'+@y+'-'+@m+'-'+@d where wk_name = @name
end
fetch next from cur into @name,@sid ,@y,@m,@d
end
close cur
DEALLOCATE cur
///以上的内容是在身份证长度为十八的基础上改的,只用一改就行了,[/color]
declare @y varchar(8),@m varchar(8),@d varchar(8)
declare cur cursor
for select 姓名,身份证号,substring(身份证号,7,2),substring(身份证号,9,2),substring(身份证号,11,2) from Sheet1$
open cur
fetch next from cur into @name,@sid,@y,@m,@d
while @@fetch_status = 0
begin
if len(@sid)=15
begin
--print 'update [192.168.123.201].htbz.dbo.t_Worker set year(wk_birthday) = '+substring(@sid,7,4)+',month(wk_birthday) = '+substring(@sid,11,2)+',day(wk_birthday) = '+substring(@sid,13,2)+' where wk_name = '+@name+''
--select replace(year(wk_birthday),@str2 = year(wk_birthday),@y), @str3= replace(month(wk_birthday),month(wk_birthday),@m) ,replace(day(wk_birthday),day(wk_birthday),@d) from [192.168.123.201].htbz.dbo.t_Worker where wk_name = @name
print @y+'-'+@m+'-'+@d
update [192.168.123.201].htbz.dbo.t_Worker set wk_birthday = '19'+@y+'-'+@m+'-'+@d where wk_name = @name
end
fetch next from cur into @name,@sid ,@y,@m,@d
end
close cur
DEALLOCATE cur
///以上的内容是在身份证长度为十八的基础上改的,只用一改就行了,[/color]