写个函数处理字符串:
create function getresult(@sfz varchar(20))
returns varchar(10)
as
begin
declare @result datetime,@csrq varchar(10)
@csrq=(case when len(@sfz)=18 then substring(@sfz,7,8) else '19'+substring(@sfz,7,6) end)
set @result=left(@csrq,4)+'-'+substring(@csrq,5,2)+'-'+right(@csrq,2)
return @result
end语句:
update tablename set birthday=dbo.getresult(sfz)
create function getresult(@sfz varchar(20))
returns varchar(10)
as
begin
declare @result datetime,@csrq varchar(10)
@csrq=(case when len(@sfz)=18 then substring(@sfz,7,8) else '19'+substring(@sfz,7,6) end)
set @result=left(@csrq,4)+'-'+substring(@csrq,5,2)+'-'+right(@csrq,2)
return @result
end语句:
update tablename set birthday=dbo.getresult(sfz)
go
insert test values ('130455721010003')
goalter table test add b as convert(datetime,case when len(a)=18 then substring(a,7,8) else '19'+substring(a,7,6) end,120)
go
select * from test
go
insert test values ('130455721010003')
insert test values ('130455197211100013')
insert test values ('124235197301110034')
goalter table test add b as convert(datetime,case when len(a)=18 then substring(a,7,8) else '19'+substring(a,7,6) end,120)
go
select * from test
我在企业管理器数据库下的“用户定义的函数”,右键“新建用户自定义函数”,将你的语句考过去,结果检查语法时出错:必须声明变量@csrq,怎么回事?
returns varchar(10)
as
begin
declare @result datetime,@csrq varchar(10)
--这边少了个SET
set @csrq=(case when len(@sfz)=18 then substring(@sfz,7,8) else '19'+substring(@sfz,7,6) end)
set @result=left(@csrq,4)+'-'+substring(@csrq,5,2)+'-'+right(@csrq,2)
return @result
end语句:
update tablename set birthday=dbo.getresult(sfz)