SQL - Calculate Age from Date of Birth
Calculating a person's age from their date of birth seems straightforward until you actually try to implement it correctly. This requirement appears everywhere: user registration systems, insurance...
Key Insights
- Never store age as a column—always calculate it from date of birth at query time to ensure accuracy and avoid data staleness
- The naive DATEDIFF approach often returns incorrect ages; you need to account for whether the birthday has occurred this year
- Each database has its own idiomatic solution: PostgreSQL’s AGE() function, MySQL’s TIMESTAMPDIFF(), and SQL Server requires manual adjustment
Calculating a person’s age from their date of birth seems straightforward until you actually try to implement it correctly. This requirement appears everywhere: user registration systems, insurance eligibility checks, age-restricted content, demographic reporting, and compliance verification. Get it wrong, and you’ll have 17-year-olds accessing adult content or 65-year-olds missing their senior discounts.
The challenge isn’t the math—it’s the edge cases. Leap years, timezone differences, and the fundamental question of whether someone’s birthday has passed this year all conspire to make a simple subtraction surprisingly tricky. Let’s walk through the correct approaches for each major database system.
The Naive Approach (And Why It Fails)
Most developers start with something like this in SQL Server:
SELECT
name,
date_of_birth,
DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age
FROM users;
This looks reasonable. Take the current date, subtract the birth date, return the difference in years. The problem? DATEDIFF only counts calendar year boundaries crossed, not actual elapsed years.
If someone was born on December 31, 2000, and today is January 1, 2024, this query returns 24. But that person is actually 23—their 24th birthday hasn’t happened yet. The function crossed 24 year boundaries, but that’s not the same as 24 complete years of life.
This bug is insidious because it’s only wrong for people whose birthdays haven’t occurred yet this year. Your tests might pass if you happen to run them after your test user’s birthday.
Accurate Age Calculation Methods
The fix requires checking whether the birthday has occurred this year and adjusting accordingly. Here’s the corrected SQL Server approach:
SELECT
name,
date_of_birth,
DATEDIFF(YEAR, date_of_birth, GETDATE()) -
CASE
WHEN DATEADD(YEAR, DATEDIFF(YEAR, date_of_birth, GETDATE()), date_of_birth) > GETDATE()
THEN 1
ELSE 0
END AS age
FROM users;
This calculates the year difference, then subtracts 1 if the birthday hasn’t occurred yet. The CASE expression adds the calculated years back to the birth date and checks if that resulting date is still in the future.
MySQL provides a cleaner solution with TIMESTAMPDIFF:
SELECT
name,
date_of_birth,
TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age
FROM users;
TIMESTAMPDIFF correctly handles the birthday check internally. It returns complete units, not boundary crossings.
PostgreSQL offers the most elegant solution with its built-in AGE function:
SELECT
name,
date_of_birth,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth)) AS age
FROM users;
The AGE function returns an interval type showing years, months, and days. EXTRACT pulls out just the years component.
Handling February 29 Birthdays
Leap year babies present a special case. When should someone born on February 29, 2000, celebrate their birthday in non-leap years? Most jurisdictions consider March 1 as their legal birthday in non-leap years, but your business rules might differ.
The standard SQL approaches handle this reasonably—they’ll consider a Feb 29 birthday as having passed once March 1 arrives. If you need different behavior, you’ll need custom logic:
-- Treat Feb 29 birthdays as Feb 28 in non-leap years
SELECT
name,
date_of_birth,
TIMESTAMPDIFF(YEAR,
CASE
WHEN MONTH(date_of_birth) = 2 AND DAY(date_of_birth) = 29
THEN DATE_SUB(date_of_birth, INTERVAL 1 DAY)
ELSE date_of_birth
END,
CURDATE()
) AS age
FROM users;
Database-Specific Implementations
Here’s a reference for each major database system:
PostgreSQL:
-- Using AGE function (recommended)
SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth))::INTEGER AS age
FROM users;
-- Alternative using date arithmetic
SELECT DATE_PART('year', CURRENT_DATE) - DATE_PART('year', date_of_birth) -
CASE
WHEN (DATE_PART('month', CURRENT_DATE), DATE_PART('day', CURRENT_DATE))
< (DATE_PART('month', date_of_birth), DATE_PART('day', date_of_birth))
THEN 1
ELSE 0
END AS age
FROM users;
MySQL:
-- Using TIMESTAMPDIFF (recommended)
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age
FROM users;
-- Alternative using date formatting trick
SELECT FLOOR(DATEDIFF(CURDATE(), date_of_birth) / 365.25) AS age
FROM users;
SQL Server:
-- Accurate calculation with birthday check
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 AS age
FROM users;
Oracle:
-- Using MONTHS_BETWEEN
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, date_of_birth) / 12) AS age
FROM users;
-- Alternative with TRUNC
SELECT TRUNC((SYSDATE - date_of_birth) / 365.25) AS age
FROM users;
The division by 365.25 approaches are approximations that work for most cases but can be off by a day in edge cases. Use the native functions when available.
Creating Reusable Functions
Encapsulating age calculation in a function ensures consistency across your application and makes maintenance easier. Here’s a PostgreSQL example:
CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE, as_of_date DATE DEFAULT CURRENT_DATE)
RETURNS INTEGER AS $$
BEGIN
IF birth_date IS NULL THEN
RETURN NULL;
END IF;
RETURN EXTRACT(YEAR FROM AGE(as_of_date, birth_date))::INTEGER;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Usage
SELECT name, calculate_age(date_of_birth) AS age FROM users;
SELECT name, calculate_age(date_of_birth, '2023-01-01') AS age_at_year_start FROM users;
For SQL Server:
CREATE FUNCTION dbo.CalculateAge(@BirthDate DATE, @AsOfDate DATE = NULL)
RETURNS INT
AS
BEGIN
IF @AsOfDate IS NULL
SET @AsOfDate = GETDATE();
IF @BirthDate IS NULL
RETURN NULL;
RETURN DATEDIFF(YEAR, @BirthDate, @AsOfDate) -
CASE
WHEN MONTH(@BirthDate) > MONTH(@AsOfDate)
OR (MONTH(@BirthDate) = MONTH(@AsOfDate) AND DAY(@BirthDate) > DAY(@AsOfDate))
THEN 1
ELSE 0
END;
END;
The as_of_date parameter is crucial for historical reporting. You’ll often need to know someone’s age at a specific point in time, not just today.
Practical Use Cases
Filtering by age range:
-- Find users eligible for senior discount (65+)
SELECT * FROM users
WHERE TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) >= 65;
-- Find users between 18 and 25
SELECT * FROM users
WHERE TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 18 AND 25;
Age-based grouping for reports:
SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 18 THEN 'Under 18'
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 18 AND 24 THEN '18-24'
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 25 AND 34 THEN '25-34'
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 35 AND 44 THEN '35-44'
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 45 AND 54 THEN '45-54'
WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 55 AND 64 THEN '55-64'
ELSE '65+'
END AS age_group,
COUNT(*) AS user_count
FROM users
GROUP BY 1
ORDER BY MIN(TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()));
Age as of a specific date:
-- Calculate age at time of purchase
SELECT
o.order_id,
u.name,
TIMESTAMPDIFF(YEAR, u.date_of_birth, o.order_date) AS age_at_purchase
FROM orders o
JOIN users u ON o.user_id = u.id;
Best Practices and Considerations
Always store date of birth, never age. Age changes daily. If you store “age: 25” in your database, it becomes stale the moment the user has a birthday. Store the birth date and calculate age when needed.
Handle NULL values explicitly. Users might not provide their birth date. Your queries should handle this gracefully rather than failing or returning nonsensical results.
Consider timezone implications. If your users span multiple timezones and you’re checking age for legal compliance (alcohol sales, age-restricted content), you need to determine whose timezone matters—the user’s or the server’s.
Index appropriately for filtered queries. If you frequently filter by age, consider a computed column or generated column that you can index:
-- SQL Server computed column
ALTER TABLE users ADD age AS (
DATEDIFF(YEAR, date_of_birth, GETDATE()) -
CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, date_of_birth, GETDATE()), date_of_birth) > GETDATE()
THEN 1 ELSE 0 END
);
Note that non-deterministic computed columns (those using GETDATE()) can’t be indexed directly. For heavy age-based filtering, consider a nightly job that updates a materialized age column, accepting that it might be off by a day.
Validate input data. Birth dates in the future or impossibly far in the past (year 1900 for a current user) indicate data quality issues. Add constraints or validation logic to catch these.
Age calculation is one of those deceptively simple problems that trips up developers regularly. Use your database’s native functions when available, encapsulate the logic in reusable functions, and always test with edge cases—especially birthdays that haven’t occurred yet this year and those pesky February 29 babies.