With this post we will calculate age with a function. This function gets a parameter with date type. Returns integer value of the age.
Simple Function
|
1 2 3 4 5 6 7 8 |
Create function fn_AgeCalc(@birthDate date) returns int as Begin Declare @age int Set @age = datediff(yy,@birthDate,getdate()) Return @age End |
Complex Function
This function produces more accurate result:
|
1 2 3 4 5 6 7 8 |
Create function fn_AgeCalc(@birthDate date) returns int as Begin IF MONTH(@birthDate) = MONTH(getdate()) and day(@birthdate)>day(getdate()) return datediff(MONTH,@birthdate, getdate())/12 - 1 return datediff(MONTH,@birthdate, getdate())/12 End |
If you want to learn difference of the functions above you can read the post “Sql Query To Find Age From Date Of Birth In Sql“
When we execute one of the code above we must get succesful message. Now we can use the function. To use the function we can use the codes belows
Example – 1 : How old is a person born on “12-05-1990”?
|
1 |
Select dbo.fn_AgeCalc('12-05-1990') |
Result: 20
Example-2 : List all students name, surname and age with ageCalc function.
|
1 |
Select name,surname,dbo.fn_ageCalc(birthdate) from students |
Result:
Example-3 List students name and surname whose age are 20
|
1 2 |
Select name,surname from students where dbo.fn_ageCalc(birthdate)=20 |
