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

Views: 4.573
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 solve this question is not enough to perform a simple DATEDIFF (YEAR, @Dt_Initial, @Dt_Final), since the function DATEDIFF (YEAR…) will return the difference of years without considering if the dates have already “completed birthday” .

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

I will demonstrate why this does not work for age calculation:

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 calculate the difference in days between the dates and divide this value by 365,25. But why this 0,25? The answer is simple .. leap years. This “technique” is authored by my friend and BI expert, 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.

Results of the functions presented

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

Results:

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.