【葉子函數分享十二】根據身份證得到生日函數

go

--建立函數(函數來自csdn，作者不詳)

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

*/