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