Click on the banner to learn about and purchase my database training on Azure

# SQL Server - How to calculate the difference in years (age) between two dates using T-SQL or CLR

Reading Time: 4 minutes

Hello people,
Alright?

In this post I would like to show how to calculate the difference in years (age) between two dates in SQL Server, showing some solutions and a performance analysis between these options in order to identify the most performative way to perform this calculation.

As you know, to resolve this issue it is not enough to perform a simple DATEDIFF (YEAR, @Dt_Inicial, @Dt_Final), since the DATEDIFF (YEAR…) function will return the years difference without considering whether the dates have already “completed birthdays” .

In a simple explanation, the DATEDIFF (YEAR…) function returns the calculation YEAR (@Dt_Final) - YEAR (@Dt_Inicial).

As you can see, it is not enough to use the DATEDIFF (YEAR ..) function to calculate age. In the first example, with only 1 day difference between dates, the function returned to me that this difference is 1 year.

In the second example, the function returns me 30 years apart, but the first date is 09 / 04 and the second is 28 / 05, that is, I haven't completed 1 year yet and the correct value would be 29 years.

## How to calculate age using T-SQL

Now I will demonstrate two simple ways for age calculation using Transact-SQL

#### Age Calculation Using Transact-SQL and CASE

To perform this calculation, we calculate the year difference between the dates and decrease 1 if the month and day of the start date is less than the month and day of the end date.

#### Age calculation using Transact-SQL and 365.25 division

To perform this calculation, we calculated the difference in days between the dates and divided this amount by 365,25. But why this 0,25? The answer is simple .. Leap years. This “technique” is authored by my friend and BI specialist, Lucas Galon.

## How to calculate age using CLR

Now I am going to demonstrate two simple ways of calculating age using CLR, which aims to achieve a great performance gain by virtue of optimizations of the C # language and the Microsoft .NET Framework.

#### Age calculation using CLR and manual calculations

To perform this calculation, we calculate the year difference between the dates and decrease 1 if the month and day of the start date is less than the month and day of the end date.

#### Age calculation using CLR and 365.25 division

To perform this calculation, we calculate the difference of days between the dates and divide this value by 365.25, as mentioned above, but now using the CLR.

## Performance Tests

Now that I've demonstrated a few different solutions for age calculation in SQL Server, I'll compare the performance of all of them and see which one is the most performative.

As I had commented in the post SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function, CLR functions generally will perform better than T-SQL functions at the same time as inline functions (without creating UDF) generally perform better than CLR functions. Let's see how each solution works out.

For these tests, I will use a base of 131.072 random dates to calculate age against the current date, and the script to generate this base I will make available below:

Test mass generation script

Test script

As expected, inline functions performed better, with CLR performing similarly and T-SQL functions taking much longer than other 2 solutions. In addition, the 365.25 split solution was better than using CASE in all 3 scenarios.

That's it folks!
I hope you have learned to calculate age correctly and with the best performance possible.
A hug and see you next.