Calculate the age in years (or months or days) for a person

Gran Canyon

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. 

Recursivity in SQL Server with Common Table Expressions

Probably you never thought that there is a nice way to make recursivity in a SQL Server Query but… yes… since CTE’s were launched in version 2005 is there.

Netherlands
Netherlands

So, I was thinking to offer a good and easy example where we can take power of this capability and found the Adventure Works DB (tested in both AdventureWorks2016 & AdventureWorks2016_EXT) is a very good example as already includes the required structures because it includes a field called OrganisationLevel that links the employee with the BusinessEntityID for the corresponding line manager.

In the sample below, we are searching for all employees with the list of managers up to the CEO. I have calculated both in text (Job Title) and Business Entity ID (Manager ID sequence), both separated by ” | “.

This functionallity opens several doors for a lot of good practises. You can nest CTE’s, use the same CTE several times within a query or even use them inside Views, SP’s or Functions and give functional names to very complex subqueries to make the code much easier to understand.

In the recent past I have used several times when need to mix complex queries with tricky table & field names. Within the CTE can extract the information required and then link more than one CTE with friendly names. The final product is very readable and much easier to maintain.

Query the System Data Dictionary to find any Table, View, SP, Function or even Column

Have you had the need to find an element in the Database but you don’t know the full detail?

Often, I remember part of the data, for example, remember that the field name contains the word Packaging, Supplier… other times, I don’t know if it is a View, SP, Table or Function but I remember that contains the word Calculate… 

to easily find them I have created the attached query that accesses the Data Dictionary. Just enter in the parameters area the Table, the Column, or both and the system will find whatever there is in the current Database.