Sql Server-实用技巧-通过身份证号来计算年龄

代码如下

IF EXISTS (SELECT * FROM sys.objects WHERE name='Fun_GetAgeByIDCardNo')
DROP FUNCTION Fun_GetAgeByIDCardNo
GO
/* =======================================
创 建 人：Lemon
创建日期：2020-05-06
功能描述：通过身份证号来计算年龄或出生日期，@isAge为0时是返回出生日期，@isAge1时返回年龄
单元名称： Fun_GetAgeByIDCardNo
======================================= */
CREATE FUNCTION Fun_GetAgeByIDCardNo(@IDCardNo NVARCHAR(30),@isAge bit)
RETURNS NVARCHAR(20)
AS
    BEGIN
        DECLARE @sResult NVARCHAR(20)=''
        DECLARE @sIDCardNo NVARCHAR(30)=''
        DECLARE @sYear INT =0
        DECLARE @Birthday NVARCHAR(30) =''
        DECLARE @Age NVARCHAR(30) =''

        SELECT @sIDCardNo=Replace(Replace(Replace(ISNULL(@IDCardNo, ''),CHAR(10),''),CHAR(13),''),CHAR(32),'')
        IF LEN(@sIDCardNo)=18 and IsDate(SUBSTRING(@sIDCardNo,7,8))=1
            BEGIN
                SELECT @Age=DATEDIFF(YEAR,SUBSTRING(@sIDCardNo,7,4),GETDATE()),@Birthday=CAST(SUBSTRING(@IDCardNo, 7, 4) AS nvarchar(4)) + N'-' + CAST(SUBSTRING(@IDCardNo, 11, 2) AS nvarchar(4)) + N'-' + CAST(SUBSTRING(@IDCardNo, 13, 2) AS nvarchar(4))
            END
        ELSE
            IF LEN(@sIDCardNo)=15 
                BEGIN
                    SELECT @sYear=CAST(SUBSTRING(@sIDCardNo,7,2) AS INT)
                    --2000年前的,年份大于17，基本上就是19XX年的
                    IF @sYear>17 and IsDate(CAST(('19'+CAST(CAST(SUBSTRING(@sIDCardNo,7,6) AS INT) AS NVARCHAR(10))) AS NVARCHAR(10)))=1
                        BEGIN
                            SELECT @Age=DATEDIFF(YEAR,CAST(('19'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)),GETDATE()),@Birthday=CAST(('19'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)) + N'-' + CAST(SUBSTRING(@IDCardNo, 9, 2) AS nvarchar(4)) + N'-' + CAST(SUBSTRING(@IDCardNo, 11, 2) AS nvarchar(4))
                        END
                    --2000年至本年度期间的，可以直接使用那两位数字
                    ELSE
                        BEGIN
                            --2010年之后的
                            IF LEN(@sYear)=2 and IsDate(CAST(('20'+CAST(CAST(SUBSTRING(@sIDCardNo,7,6) AS INT) AS NVARCHAR(10))) AS NVARCHAR(10)))=1
                                BEGIN
                                    SELECT @Age=DATEDIFF(YEAR,CAST(('20'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)),GETDATE()),@Birthday=CAST(('19'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)) + N'-' + CAST(SUBSTRING(@IDCardNo, 9, 2) AS nvarchar(4)) + N'-' + CAST(SUBSTRING(@IDCardNo, 11, 2) AS nvarchar(4))
                                END
                            --2000年至2009年的
                            IF LEN(@sYear)=1 and IsDate(CAST(('200'+CAST(CAST(SUBSTRING(@sIDCardNo,7,6) AS INT) AS NVARCHAR(10))) AS NVARCHAR(10)))=1
                                BEGIN
                                    SELECT @Age=DATEDIFF(YEAR,CAST(('200'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)),GETDATE()),@Birthday=CAST(('19'+CAST(@sYear AS NVARCHAR(10))) AS NVARCHAR(10)) + N'-' + CAST(SUBSTRING(@IDCardNo, 9, 2) AS nvarchar(4)) + N'-' + CAST(SUBSTRING(@IDCardNo, 11, 2) AS nvarchar(4))
                                END
                        END
                END
        IF @isAge=0 
            set @sResult=@Birthday
        ELSE
            set @sResult=@Age

        RETURN @sResult
    END
GO