Get Age and Gender From Person number
Get Age from Person number
To get Age of a person from his 6 digit of person number (e.g 440502-xyzw) your need to create a function in sql like the following code:
CREATE FUNCTION [dbo].[AlderFranPersonNr]
(
@Personnr varchar(11)
)
RETURNS int
AS
BEGIN
declare @result as int
declare @fodelsestr as nvarchar(8)
declare @brytpunkt as nvarchar(6)
select @brytpunkt=convert(nvarchar(10),DATEADD(YY,-18,getdate()),12) --- here we get an 18 years limit (051002) (want to get only over 18 years)
select @fodelsestr=iif(Try_CAST('1'+ @brytpunkt as int) < Try_CAST('1'+ substring(@personnr,1,6 )as int),'19','20')+ substring(@personnr,1,6)
if isdate(@fodelsestr)<>1
return null
else
select @result=datediff(year,cast(@fodelsestr as date) ,getdate())
return @result
END
GO
Description:
In line: 11, we get @brytpunkt for 18 years
In line 12: if 1+@brytpunkt <1+ personnr then we add 19 otherwise 20 in the beginning and asign it to the @fodelsestr
In line: 16 we getdate – the date birthday of person and returns the age of person.
You have to a database table with column : personnr in the form: abcdef-ghkl (e.g: 670807-2345) and send this personnr as a parameter to your function.
to test your function run the following query:
select [dbo].[AlderFranPersonNr]('590707-1836')
then give you 64
Get Gender from person number using SQL query
To get gender of a person from person number you need to check the 10th characther of person number as shown in the following SQL query:
CASE WHEN (Personnr NOT LIKE '%*%') AND Try_CAST(SUBSTRING(Personnr, 10, 1) as Integer)%2 = 0 then 'Woman' ELSE
CASE WHEN (Personnr NOT LIKE '%*%') AND Try_CAST(substring(Personnr,10,1) as Integer)%2 = 1 then 'Man' ELSE
CASE WHEN ( Personnr LIKE '%*%' then 'Unknwon' END
END
END GenderDescription:
Finds the 10th character of person number:
If it has reminder when divide by 2 is 0 then person is Woman (e.g.: 340102-4563 here 6 is dividable to 2)
If it has reminder when divide by 2 is 1 then person is Man (e.g. 450412-6431 here 3 is not dividable to 2)
If there is any ‘*’ in the last 4 characters then gender is unknown (e.g. 550911-7**2 here there is * in the last 4 characters)
You need have a DB table with a column person number. you can user the above query as an inner select.
Conclusion
In this post we have showed how to create a function in SQL DB and give a parameter as person number to calculate persons age. Even we can find the gender of a person via personnr via SQL query to check the 10 character of personnr.