A common requirement in SQL Server databases is to calculate the age of something in years. There are several techniques for doing this depending on how accurate you want the final result to be. This blog considers three techniques, saving the most complex, but most accurate, for last.

## Using DATEDIFF to Calculate Age

Apparently, the quickest and easiest way to calculate the age of someone or something in years is to simply use the **DATEDIFF** function.

At first glance the **DATEDIFF** function seems to successfully calculate ages quickly and easily, but closer inspection reveals that it’s not quite as accurate as we’d like (and certainly not as accurate as Keanu Reeves would like!). In fact, in the above list, only the last record is calculated correctly – the other three are being reported as one year older than they actually are.

The problem is that when the **DATEDIFF **function is told to look at years it ignores every other part of the dates involved, essentially performing the calculation shown below:

**DATEDIFF**is really doing when you ask it to give you the difference between two dates in years.

Clearly, using ** DATEDIFF **to calculate the difference between dates in years isn’t accurate enough for something as potentially sensitive as age. In that case we need a different approach.

## Calculating Age in Days and Dividing the Result

A more accurate, but not perfect, way to calculate age in years is to first work out the difference in days between two dates and then divide the result by the number of days in a year. The method is shown in the example below:

**.25**is to take into account leap years.

The last step in this type of calculation is to remove the decimal places to give the age in whole years. To do this we can convert the answer to the **INT** data type.

**INT**data type gives us the age in years.

Clearly this method is more accurate than using **DATEDIFF** with years, but we’re still not one hundred percent there.

The problem with this method is that smaller date ranges will give us a less accurate answer, as in the example shown above. Fortunately, there’s one further method that we can use to calculate age in years correctly.

## Using DATEDIFF and Correcting the Result

This is the most accurate way to calculate an age in years, but it’s also the most complex expression to write. The starting point is to use the first calculation we demonstrated at the top of the page to calculate the difference in years between two dates:

**DATEDIFF**calculation reports some dates as a year older than they actually are.

The next step is to incorporate the **DATEADD** function into the expression to add the calculated number of years to the original date:

The result of the above calculation is the date on which the person or thing reaches the age that the **DATEDIFF** function calculates. The final step is to work out whether that date is after today’s date, and if so subtract 1 from the age that **DATEDIFF** calculates. We can use the **CASE** statement to do this as follows: