User-defined function to calculate age at specified date

Question

User-defined function to calculate age at specified date

Aidan Devlin 21

Hi, I need a user-defined function in SQL that will calculate the age of someone at a provided date. The user input to the function should be a person's ID number and a specified date. The date of birth is confidential and so cannot be used or seen by the user.

I have the calculation for Age, which I will post below, I am just unsure of how to create the function due to the sensitivity and confidentiality of the date of birth.

Thanks,
Aidan

CASE
WHEN dateadd(year, datediff (year, i.DATE_OF_BIRTH, getdate()), i.DATE_OF_BIRTH) > getdate()
THEN datediff(year, i.DATE_OF_BIRTH, getdate()) - 1
ELSE datediff(year, i.DATE_OF_BIRTH, getdate())
END AS CURRENT_AGE

SSMS v18.2

Accepted answer

0 additional answers

Your answer

Answer 1

Viorel 122.6K

Maybe define it like this:

create function GetAge( @PersonID int )  
returns int   
as  
begin  
    declare @age int  
  
    select @age =   
        CASE  
        WHEN dateadd(year, datediff (year, i.DATE_OF_BIRTH, getdate()), i.DATE_OF_BIRTH) > getdate()  
        THEN datediff(year, i.DATE_OF_BIRTH, getdate()) - 1  
        ELSE datediff(year, i.DATE_OF_BIRTH, getdate())  
        END   
    from Person i  
    where PersonID = @PersonID  
  
    return @age  
end

It returns the age, but does not reveal the date of birth.

Aidan Devlin 21 Reputation points

2022-12-01T09:38:04.34+00:00

Thank you, that's great for the current age. How could I add another input that will give me that person's age at future dates. I'm hoping to make a dynamic function that will have different date requirements going forward to calculate a person's age a different dates.

Thank you,
Aidan

Viorel 122.6K

Add a parameter:

create or alter function GetAge( @PersonID int, @some_date date )  
returns int   
as  
begin  
    declare @age int  
  
    select @age =   
        CASE  
        WHEN dateadd(year, datediff (year, i.DATE_OF_BIRTH, @some_date), i.DATE_OF_BIRTH) > @some_date  
        THEN datediff(year, i.DATE_OF_BIRTH, @some_date) - 1  
        ELSE datediff(year, i.DATE_OF_BIRTH, @some_date)  
        END   
    from Person i  
    where PersonID = @PersonID  
  
    return @age  
end

Aidan Devlin 21 Reputation points

2022-12-01T10:16:26.69+00:00

Thanks again, @Viorel great help.

Stéphane BILLES 6

Just a side note, on a project we had same kind of requirement (compute some kind of expiration date based on multiple columns) and to make things easier to get the data we added a computed column.
So create a function just to compute the year diff like :

create or alter function Diff_In_Years ( @birthdate date, @some_date date )  
 returns int   
 as  
 begin  
     declare @age int      
     select @age =   
         CASE  
         WHEN dateadd(year, datediff (year, @birthdate, @some_date), @birthdate) > @some_date  
         THEN datediff(year, @birthdate, @some_date) - 1  
         ELSE datediff(year, @birthdate, @some_date)  
         END   
      
     return @age  
 end

then add the column to Person table :

alter table Person  
  add CurrentAge as Diff_In_Years(DATE_OF_BIRTH, getdate())

this will allow to grab data as easy as : select PersonID, Name, OtherInfo, CurrentAge from Person where ...

Aidan Devlin 21 Reputation points

2022-12-02T11:10:45.203+00:00

Great, thanks for your help. Much appreciated!

Share via

User-defined function to calculate age at specified date

0 additional answers

Your answer