- Dedicated trainers
- Maximum 6 per class
- Superb courseware

Phone (01457) 858877 or email

Dates and times can be the most frustrating data types to work with in SQL Server. Learn everything you need to know about the way dates work with this handy blog series and enjoy happier times!

- Dates and Times in SQL
- Date and Time Data Types in SQL Server
- Dates and Times in Criteria
- Formatting Dates and Times in SQL Server
- Calculations with Dates in SQL Server
- How to Calculate Age in SQL Server (this article)

This blog is part of a larger tutorial on SQL Server, which you can read here. Wise Owl also run SQL courses, as well as training in SSRS, SSAS and SSIS.

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.

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

A seemingly quick and obvious way to calculate age in years.

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:

This is what
**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.

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:

Dividing the age in days by the number of days in a year gives a
slightly more accurate result. The **.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.

Converting the result of the above calculation to the
**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.

Here the date range doesn't include a leap year so we're inaccurately reporting the age as a year younger than it should be.

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.

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:

The original
**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:

This calculation tells us the date on which the event reaches the age shown in the third column.

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:

Finally, an accurately calculated age!

The expression is quite difficult to read, but it is the most accurate way to calculate an age in years in SQL Server. Phew!

Dates and times can be the most frustrating data types to work with in SQL Server. Learn everything you need to know about the way dates work with this handy blog series and enjoy happier times!

- Dates and Times in SQL
- Date and Time Data Types in SQL Server
- Dates and Times in Criteria
- Formatting Dates and Times in SQL Server
- Calculations with Dates in SQL Server
- How to Calculate Age in SQL Server (this article)

This blog is part of a larger tutorial on SQL Server, which you can read here. Wise Owl also run SQL courses, as well as training in SSRS, SSAS and SSIS.

Comments on this blog

This blog has 2 comments:

Comment added by larrymcp on 07 November 2012 at 17:56 GMT

Five quid for saving me time!

If you had a "Donate" button, I probably would've gone ahead and send a contribution.

:)

If you had a "Donate" button, I probably would've gone ahead and send a contribution.

:)

Reply from Andrew Gould (blog author)

Just happy to hear it helped.

Although you may be interested to know that we also gladly accept cheques and cash in the post!

Comment added by Jan-Willem Lankhaar on 21 June 2013 at 12:09 GMT

Hi Andrew,

Thanks for sharing. You inspired me to come up with two alternatives:

DATEDIFF(yy, @d1, @d2) - CEILING((DATEPART(dy, @d1) - DATEPART(dy, @d2))/365.242199) -- Without a case

and

DATEDIFF(yy, @d1, @d2) -

CASE

WHEN DATEPART(dy, @d2) < DATEPART(dy, @d1) THEN 1

ELSE 0

END -- Same line of reasoning as yours but easier to understand.

with @d1 the oldest of the two dates and @d2 the newest. Note that I use 365.242199 instead of 365.25 as the number of days in a year. Although this is more accurate than 365.25, it may for small time differences (i.e. few years) still yield slightly inaccurate results.

Kind regards,

Jan-Willem Lankhaar

Reply from Andrew Gould (blog author)

Yeah, very good! Definitely prefer the second method - I'll take accuracy over easiness any day of the week!