How to Calculate Age between Date of Birth and Today in SQL Server

As a programmer or developer, you will often come across tasks where you’re required to calculate a person’s age. This task seems straightforward but can be trivia. However, in this tutorial, I will teach you how to calculate the age between a given date of birth and today’s date in SQL Server.

Before we get started, you need to be familiar with the SQL function DATEDIFF. This function allows you to calculate the period between two dates. This period can be years, months, or days.

DATEDIFF(interval, starting_date, ending_date)

The function returns an integer which is the value of the difference between the start date and the end date.

Visit the Microsoft documentation page to learn more about the DATEDIFF function.

The less accurate formula for calculating age in SQL Server

DATEDIFF(year, date_of_birth, GETDATE())

This formula is simple but not accurate. For example, let’s consider these two dates.

Let’s assume today’s date is 2023-08-04 and you need to calculate a person’s age who was born on 1993-08-05.

-- assume today's date (GETDATE()) value is 2023-08-04'
SELECT DATEDIFF(year, '1993-08-05', GETDATE())

The result is 30.

However, this person’s age is supposed to be 29. This is why we need a more accurate way to calculate age.

The more accurate formula for calculating age in SQL Server

--assume GETDATE() = '2023-08-04'
DECLARE @date_of_birth date = '1993-08-05'
SELECT DATEDIFF(year, @date_of_birth, GETDATE()) - CASE WHEN (MONTH(@date_of_birth) > MONTH(GETDATE())) OR (MONTH(@date_of_birth) = MONTH(GETDATE()) AND DAY(@date_of_birth) > DAY(GETDATE())) THEN 1 ELSE 0 END

The code above is a more accurate way to calculate the age between two dates. It checks if the month or day has passed. If yes, it subtracts one else it subtracts 0.

Therefore, the result which is 29 is accurate.

How to calculate age between two dates in SQL Server

Modify the code above. Change GETDATE() to a specific date.

DECLARE @date_of_birth DATE = '1993-08-05', @today DATE = '2023-08-04' 
SELECT DATEDIFF(year, @date_of_birth, @today) - CASE WHEN (MONTH(@date_of_birth) > MONTH(@today)) OR (MONTH(@date_of_birth) = MONTH(@today) AND DAY(@date_of_birth) > DAY(@today)) THEN 1 ELSE 0 END

I hope you found this article helpful. Learn how you can group data by time in SQL Server.