This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Dates and Times in SQL Server Part six of a six-part series of blogs
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 blog) 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.

Dates and Times in SQL Server

Part six of a six-part series of blogs

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 blog)

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.

Posted by Andrew Gould on 13 February 2012

How to Calculate Age in SQL Server

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.

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.

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:

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.

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:

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!

If you've learnt something from this blog, think how much more we could teach you about SQL! If you can't make it to one our classroom courses in the UK, you can sit on our live two-day online Introduction to SQL or two-day advanced SQL course anywhere in the world.

Parts of this blog
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 blog)

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	SQLTrainee
When:	15 Oct 20 at 09:51

Reply to SQLTrainee

Hello, I have used your query to calculate age. I am wondering how to sort the newly created AS field. When I am attempting to use a WHERE clause it tells me "Conversion failed when converting the varchar value 'Age' to data type int.

The expression I used is the same formula you provided. At the end of my query I wanted to sort the results using

WHERE 'Age' > 20 ;

and I get "Conversion failed when converting the varchar value 'Age' to data type int.

From:	Andy B
When:	15 Oct 20 at 09:58

Reply to Andy B

The Age field can be referenced without punctuation, as it doesn't contain a space. Alternatively, you can put square brackets round it like this: [Age]. What you can't do is put quotation marks, as what you're doing is sorting by the word "Age". Because this is a string of text, SQL can't compare it to an integer.

From:	SQLTrainee
When:	15 Oct 20 at 10:22

Reply to SQLTrainee

Age in the where clause isn't recognizing the created column. This is my query that I was working on it's from the Viescas Book "SQL Queries for Mere Mortals"

SELECT CONCAT(Students.StudFirstName, ' ' ,Students,StudLastName) AS StudentName,
DATEDIFF(YY,Students.StudBirthDate,SYSDATETIME()) -
CASE
WHEN DATEADD(YY,DATEDIFF(YY,Students.StudBirthDate,SYSDATETIME()),Students.StudBirthdate) > SYSDATETIME() THEN 1 ELSE 0
END AS [Age]
FROM Students
INNER JOIN Majors ON Majors.MajorID = Students.StudMajor
WHERE [Age] > 20;

"Invalid column name 'Age' is what it says when I hover over the "error"

From:	Andy B
When:	15 Oct 20 at 10:30

Reply to Andy B

OK, your problem is nothing to do with age calculation. You can't use an alias for a column in a WHERE clause, only in an ORDER BY clause. There are various solutions: repeat the expression instead of using the Age alias, create an intermediate view or (my personal favourite) create a CTE and divide the problem into two parts. And with that, good luck!

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC