SQL Server calculates birth date and age stored procedures based on a social security number

I have a business here that requires the customer to fill in the ID number, automatically calculate his birth date and age

In SQL, the specific stored procedure implementation is this:

/*********************************************** date and age of birth based on social Security number DATE:2017-10-15AUTHOR:XZL condition: ID number return: Date of birth and age * * * ********************************************//** * * Date of birth and age based on ID number * **/Create proc [dbo].[Sp_getbirthdate_age]    @IDCard varchar( -),    @BirthDate varchar( -) output,@Age intOutput asbegin    --declaring variables--    Declare @BirthDateStr varchar(Ten)     Declare @YearStr varchar(Ten)    Declare @MonthStr varchar(Ten)    Declare @DayStr varchar(Ten)        Declare @NowDateStr varchar( -)    Declare @AgeStr varchar(Ten)        ----1, according to the ID number, calculate the date of birth---    --Setting the Idcard value    --Set @IDCard = ' 445182199403123781 '    --1) Obtain the ID number of the month and day part (return: 19940312)--    Set @BirthDateStr = SUBSTRING(@IDCard,7,8)    --2) Convert the acquired date string to the corresponding day format--    --2.1) Get the year part--    Set @YearStr = SUBSTRING(@BirthDateStr,1,4)    --2.2) Get the month part--    Set @MonthStr = SUBSTRING(@BirthDateStr,5,2)    --2.3) Get the day part--    Set @DayStr = SUBSTRING(@BirthDateStr,7,2)    --3) Returns the date after the combination---    Set @BirthDate = @YearStr +'-'+@MonthStr+'-'+@DayStr        --2, according to the date of birth and the current date, calculate the age--    --1) Gets the date part of the current time (return: 2017-10-15)--    Set @NowDateStr = CONVERT(varchar(Ten),GETDATE(), at)    --2) Get the current date and date of birth (age) return: 23 years old--    Set @AgeStr = DATEDIFF( Year,@BirthDate,@NowDateStr)        --3) Determine the current date and date of birth (whether birthday, not birthday minus 1 years old)--    if(SUBSTRING(@BirthDate,6,5)<= SUBSTRING(@NowDateStr,6,5))       begin             --Cast () converts a character to a numeric function            Set @Age = CAST(@AgeStr  as int)        End    Else                   begin           Set @Age = CAST(@AgeStr  as int)-1      End    --output calculated return result--    Select @BirthDate  asDate of birth@Age  asAgeEnd 

In SQL, you can see the stored procedures created above in programmability--stored procedures

Call to the stored procedure:

--Test 1:---The date of birth is past the current dateDeclare @IDCard varchar( -)Declare @BirthDate varchar( -)Declare @Age intSet @IDCard='445182199410103781'execSp_getbirthdate_age@IDCard,@BirthDateOutput@AgeOutput--return Results----Date of birth: 1994-10-10 Age:

--Test 2:---The date of birth has not been past the current dateDeclare @IDCard varchar( -)Declare @BirthDate varchar( -)Declare @Age intSet @IDCard='445182199410183781'execSp_getbirthdate_age@IDCard,@BirthDateOutput@AgeOutput--return Results----Date of birth: 1994-10-18 Age:

Result after call:

SQL Server calculates birth date and age stored procedures based on a social security number