MS SQL 获取身份证年龄

ALTER FUNCTION [dbo].[Get_IDCard_Age2]
( @pspt_id VARCHAR(18)
)
RETURNS int -- nvarchar
AS
BEGIN
declare @age int = -1

if len(@pspt_id)<>‘18‘
or substring(@pspt_id,1,17) not between ‘10000000000000000‘ and ‘99999999999999999‘
or @pspt_id like ‘%.%‘
or ISNUMERIC(substring(@pspt_id,1,17))=0
or substring(@pspt_id,7,4) not between ‘1900‘ and ‘2019‘
or substring(@pspt_id,11,2) not between ‘01‘ and ‘12‘
or substring(@pspt_id,13,2) not between ‘01‘ and ‘31‘
or isdate(substring(@pspt_id,7,8))=0
return -1

if (substring(@pspt_id,1,1)*7+
substring(@pspt_id,2,1)*9+
substring(@pspt_id,3,1)*10+
substring(@pspt_id,4,1)*5+
substring(@pspt_id,5,1)*8+
substring(@pspt_id,6,1)*4+
substring(@pspt_id,7,1)*2+
substring(@pspt_id,8,1)*1+
substring(@pspt_id,9,1)*6+
substring(@pspt_id,10,1)*3+
substring(@pspt_id,11,1)*7+
substring(@pspt_id,12,1)*9+
substring(@pspt_id,13,1)*10+
substring(@pspt_id,14,1)*5+
substring(@pspt_id,15,1)*8+
substring(@pspt_id,16,1)*4+
substring(@pspt_id,17,1)*2
)%11 <> (
case
when substring(@pspt_id,18,1)=‘1‘ then ‘0‘
when substring(@pspt_id,18,1)=‘0‘ then ‘1‘
when substring(@pspt_id,18,1) in (‘X‘,‘x‘) then ‘2‘
when substring(@pspt_id,18,1)=‘9‘ then ‘3‘
when substring(@pspt_id,18,1)=‘8‘ then ‘4‘
when substring(@pspt_id,18,1)=‘7‘ then ‘5‘
when substring(@pspt_id,18,1)=‘6‘ then ‘6‘
when substring(@pspt_id,18,1)=‘5‘ then ‘7‘
when substring(@pspt_id,18,1)=‘4‘ then ‘8‘
when substring(@pspt_id,18,1)=‘3‘ then ‘9‘
when substring(@pspt_id,18,1)=‘2‘ then ‘10‘
end
)
return -1

if len(rtrim(ltrim( REPLACE(REPLACE(REPLACE(REPLACE(substring(@pspt_id,7,8),
CHAR(13),‘‘),
CHAR(10),‘‘),‘ ‘,‘‘),
CHAR(13) + CHAR(10),‘‘)))
)<>8
return -1

if isdate(substring(@pspt_id,7,8))=1
return datediff(YEAR, substring(@pspt_id,7,8),GETDATE())

return @age
END

MS SQL 获取身份证年龄

原文：https://www.cnblogs.com/ilookbo/p/10882975.html