A common problem in T-SQL is to calculate the age for a person. If you are not very familiar with the Date functions can be more complex than initially looks as there is no a standard function to calculate it.
Anyway, if you think deeply, it is simple if we can calculate separately first the difference in years between today and the birthdate. Then, if the birthdate has been reach we leave as it is, otherwise, we just decrease one year.
I have prepared the corresponding script that uses AdventureWorks2016_EXT (also works in
AdventureWorks2016) and to make it easier to follow the calculations, first shows the BirthDate and Today’s date, then, calculates the difference in years and then, in the 4th column calculates the age as the years of difference but decreasing in one if today’s date as in the year of birth is bigger than the birthdate. Finally, just using the standard functions, it calculates the age in Days and Months.
I hope you will enjoy it, the provided query contains some additional comments:
We can think of creating a function to calculate that but the performance can be affected negatively as calling functions creates some overhead. In further articles we will investigate how much affects for this calculation.