Calculate Age from DOB in MySQL

Age from a birth date

A very common need is turning a stored date of birth into an age in years. It sounds simple, but doing it wrong gives ages that are off by one for much of the year. MySQL's TIMESTAMPDIFF does it correctly and easily.

Why year subtraction fails

-- WRONG
SELECT YEAR(CURDATE()) - YEAR('2000-12-25');   -- gives 26 in 2026
-- but the birthday on Dec 25 has not happened yet!

Subtracting years ignores the month and day. Someone born in December is not yet a year older until their birthday. This method is wrong for most of the year.

The right way: TIMESTAMPDIFF

SELECT name, dob,
    TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM students;

TIMESTAMPDIFF(YEAR, dob, CURDATE()) counts complete years between the birth date and today, correctly handling whether this year's birthday has passed.

Using CURDATE()

CURDATE() returns today's date, so the age is always current — it updates automatically each day without you changing anything. You could also compare against a fixed date by replacing CURDATE() with a specific date, for example to find age as of an exam date.

Filtering by age

SELECT name
FROM members
WHERE TIMESTAMPDIFF(YEAR, dob, CURDATE()) >= 18;

Put the age calculation in the WHERE clause to filter. This returns all members who are 18 or older, computing each one's age from their date of birth on the fly.

Age in months or days

SELECT name,
    TIMESTAMPDIFF(MONTH, dob, CURDATE()) AS age_months,
    TIMESTAMPDIFF(DAY, dob, CURDATE()) AS age_days
FROM babies;

Change the first argument to MONTH or DAY for finer detail. This is handy for infants, memberships, or any case where whole years are too coarse.

Common mistakes

• Subtracting years only. It ignores month and day, giving wrong ages.
• Getting the argument order wrong. It is (unit, start, end) — dob then CURDATE().
• Storing dob as text. Use a DATE column so date functions work.
• Forgetting CURDATE() updates daily. That is a feature — age stays current.

Summary

• Calculate age with TIMESTAMPDIFF(YEAR, dob, CURDATE()).
• Plain year subtraction is wrong — it ignores month and day.
• CURDATE() keeps the age current automatically.
• Filter by age by putting the calculation in WHERE.
• Use MONTH or DAY units for finer detail.

Next, find month boundaries in first and last day of a month, or recent rows in records from the last 7 days.