SQL函数根据身份证得到生日函数

go

create function [dbo].[Get_birthday]

(

    @idcardno nvarchar(50)

)

returns varchar(10)

as

begin

    declare @birthday datetime

 

if (len(@idcardno)=15 or len(@idcardno)=16) and substring(@idcardno,9,2) between 1 and 12   and substring(@idcardno,11,2) between 1 and 31

 set @birthday= convert(varchar(10),'19'+substring(@idcardno,7,2)+'-'+substring(@idcardno,9,2)+'-'+substring(@idcardno,11,2),120)

else if len(@idcardno)=18 and substring(@idcardno,7,2)>=19 and substring(@idcardno,11,2) between 1 and 12   and substring(@idcardno,13,2) between 1 and 31

 set @birthday= convert(varchar(10),substring(@idcardno,7,4)+'-'+substring(@idcardno,11,2)+'-'+substring(@idcardno,13,2),120)

else

 set @birthday=null

 return(convert(varchar(10),@birthday,120))

end

 

--测试示例

select dbo.[Get_birthday]('222222198306043213')

 

--运行结果

/*

1983-06-04

*/