Calculate the Next Birthday in MySQL

The next birthday is determined by a calendar date, not by a duration. Start with the birth month and day in the reference year. If that date has already passed, move it forward by one year.

A compact MySQL expression can implement exactly that rule. First, shift the original birth date into the year of an explicit reference date:

DATE_ADD(
    birth_date,
    INTERVAL YEAR(as_of_date) - YEAR(birth_date) YEAR
)

This produces the birthday candidate for the reference year while keeping the calculation in MySQL's date domain. There is no need to assemble and parse a date string.

Select This Year or Next Year

MySQL evaluates a comparison as 0 when it is false and 1 when it is true. The test candidate < as_of_date can therefore be added directly to the number of elapsed calendar years:

DATE_ADD(
    birth_date,
    INTERVAL (
        YEAR(as_of_date) - YEAR(birth_date)
        + (candidate < as_of_date)
    ) YEAR
)

If the birthday is today or still ahead, the comparison contributes zero. If it has already passed, it contributes one additional year. Applying the complete interval to the original birth date also preserves February 29 whenever the target year is a leap year. This avoids a CASE expression for the year selection without hiding the underlying rule.

A query can calculate the next birthday for every person using a common table expression:

WITH birthday_candidates AS (
    SELECT
        id,
        name,
        birth_date,
        DATE_ADD(
            birth_date,
            INTERVAL YEAR(CURDATE()) - YEAR(birth_date) YEAR
        ) AS candidate
    FROM person
    WHERE birth_date <= CURDATE()
)
SELECT
    id,
    name,
    DATE_ADD(
        birth_date,
        INTERVAL (
            YEAR(CURDATE()) - YEAR(birth_date)
            + (candidate < CURDATE())
        ) YEAR
    ) AS next_birthday
FROM birthday_candidates;

CURDATE() is evaluated once per statement and follows the MySQL session time zone. Reports and tests become reproducible when they use an explicit reference date instead.

A Deterministic Function

Passing the reference date as an argument separates the calendar calculation from the database clock:

DELIMITER //

CREATE FUNCTION next_birthday(
    birth_date DATE,
    as_of_date DATE
)
RETURNS DATE
DETERMINISTIC
NO SQL
BEGIN
    DECLARE candidate DATE;

    IF birth_date IS NULL
       OR as_of_date IS NULL
       OR birth_date > as_of_date THEN
        RETURN NULL;
    END IF;

    SET candidate = DATE_ADD(
        birth_date,
        INTERVAL YEAR(as_of_date) - YEAR(birth_date) YEAR
    );

    RETURN DATE_ADD(
        birth_date,
        INTERVAL (
            YEAR(as_of_date) - YEAR(birth_date)
            + (candidate < as_of_date)
        ) YEAR
    );
END//

DELIMITER ;

The guard returns NULL for missing values and for a birth date later than the reference date. The function is deterministic because every value that affects the result is supplied as an argument. For the current date, call it as next_birthday(birth_date, CURDATE()).

A fixed reference date demonstrates all three branches around the birthday:

SELECT
    next_birthday('2000-08-20', '2026-08-21') AS already_passed,
    next_birthday('2000-08-21', '2026-08-21') AS birthday_today,
    next_birthday('2000-08-22', '2026-08-21') AS still_ahead;

+----------------+----------------+-------------+
| already_passed | birthday_today | still_ahead |
+----------------+----------------+-------------+
| 2027-08-20     | 2026-08-21     | 2026-08-22  |
+----------------+----------------+-------------+

Here, “next” includes today. To require a date strictly after the reference date, change the comparison in the return expression from < to <=.

Why the TO_DAYS Shortcut Fails

The following compact function may look as though it derives the next anniversary from the number of elapsed years:

CREATE FUNCTION nextbday(BDAY DATE)
RETURNS DATE
RETURN DATE_ADD(
    BDAY,
    INTERVAL YEAR(
        DATE_SUB(NOW(), INTERVAL TO_DAYS(BDAY) + 1 DAY)
    ) + 1 YEAR
);

It does not compare the birthday candidate with the current date. For a birth date of 2000-08-20 and a current date of 2026-08-21, it returns 2026-08-20, which is already in the past. The arithmetic effectively reconstructs an anniversary in the current year, but it cannot decide whether that anniversary has passed.

TO_DAYS() also introduces an indirect day-number calculation where direct calendar arithmetic is clearer. The candidate-and-comparison method states both required operations explicitly and works across December-to-January transitions without special handling.

February 29 Requires a Policy

When adding whole years to February 29, DATE_ADD() adjusts a nonexistent February 29 to the last valid day of the month. The function therefore treats February 28 as the birthday in a non-leap year:

SELECT
    next_birthday('2000-02-29', '2025-02-27') AS before_birthday,
    next_birthday('2000-02-29', '2025-03-01') AS after_birthday,
    next_birthday('2000-02-29', '2027-03-01') AS next_leap_year;

+-----------------+----------------+----------------+
| before_birthday | after_birthday | next_leap_year |
+-----------------+----------------+----------------+
| 2025-02-28      | 2026-02-28     | 2028-02-29     |
+-----------------+----------------+----------------+

Some legal or business rules use March 1 instead. That choice cannot be inferred from a date alone and should be encoded as an explicit application policy.

Days until the Next Birthday

Once the next birthday is available, DATEDIFF() gives the number of calendar days remaining:

SELECT
    name,
    next_birthday(birth_date, CURDATE()) AS next_birthday,
    DATEDIFF(
        next_birthday(birth_date, CURDATE()),
        CURDATE()
    ) AS days_remaining
FROM person;

The result is zero on the birthday because the inclusive definition returns today's date. Keeping the reference date explicit inside the reusable function also makes boundary tests independent of when they are executed.

References

• MySQL Oracle, MySQL 8.4 Reference Manual: Date and Time Functions