Upcoming Birthdays in the Next 30 Days With T-SQL

December is a bad time to discover that your birthday reminder ignores January. Finding upcoming birthdays means calculating the next actual birthday date, then comparing dates. The same calculation also needs a clear rule for February 29.

Decide What the Upcoming Birthdays Window Includes

Before writing the filter, settle what “next 30 days” means. This example includes today and excludes the date exactly 30 days later. With date values, that gives you 30 calendar dates. If the business wants tomorrow through 30 days from now, move both boundaries forward. Do not mix those definitions across reports.

I check the date boundary before I check the birthday formula. A reminder that appears a day late still looks perfectly sorted. Use a date variable instead of comparing a birth date with the current timestamp. That keeps the clock portion out of the decision.

Which date counts as today for your readers? GETDATE uses the server clock. For a business in another time zone, pass the business date into the query. The examples use a fixed date so you can inspect the year-end behavior. Replace it with CONVERT(date, GETDATE()) for your own daily run.

Move the Original Birth Date Forward

Store the full birth date as a date column. The birth year lets DATEADD calculate the offset into the current year. DATEDIFF(year, birth_date, @today) counts year boundaries. Here that is exactly the offset we want, rather than a calculation of somebody’s age.

The first expression builds a candidate in the current year. If that date has passed, the second expression builds a candidate in the next year. Notice that both expressions start from the original birth date. That detail matters for leap birthdays. Adding a year to an already adjusted February 28 loses the original February 29 intent.

This self-contained example uses invented sample records. It does not read or change your employee table. Run it first, then replace the table variable with your real source. Keep the person identifier in the output. Names alone are a poor key, even when everybody promises to be unique.

DECLARE @today date = '20231220';
DECLARE @People table
(
    PersonID int PRIMARY KEY,
    DisplayName nvarchar(100),
    birth_date date NOT NULL
);
INSERT @People VALUES
(1, N'Alex', '19900105'),
(2, N'Jordan', '19851225'),
(3, N'Taylor', '20000229');
WITH Candidates AS
(
    SELECT PersonID, DisplayName, birth_date,
        DATEADD(year, DATEDIFF(year, birth_date, @today),
            birth_date) AS BirthdayThisYear
    FROM @People
    WHERE birth_date <= @today
), NextDates AS
(
    SELECT PersonID, DisplayName,
        CASE WHEN BirthdayThisYear < @today
             THEN DATEADD(year,
                  DATEDIFF(year, birth_date, @today) + 1, birth_date)
             ELSE BirthdayThisYear END AS NextBirthday
    FROM Candidates
)
SELECT PersonID, DisplayName, NextBirthday,
    DATEDIFF(day, @today, NextBirthday) AS DaysUntilBirthday
FROM NextDates
WHERE NextBirthday >= @today
  AND NextBirthday < DATEADD(day, 30, @today)
ORDER BY NextBirthday, PersonID;

Make February 29 a Business Decision

DATEADD(year, …) maps February 29 to February 28 when the target year has no February 29. That is the policy used in the first query. In a leap year, the original date produces February 29 again. You do not need to replace the stored birth date.

Some reminder systems celebrate that birthday on March 1 instead. Neither convention belongs hidden in a developer’s guess. Ask the person responsible for the reminder and record the choice. Birthday greetings, eligibility rules, and legal age calculations have different purposes. This query is a reminder list, not a legal age determination.

I have seen calendar logic become confusing because a display rule changed the source data. Keep the original birth date intact. Apply the chosen anniversary rule only while calculating the occurrence. A calendar is quite capable of creating trouble without our help. Test both leap and non-leap target years before accepting the result.

Use March 1 Without Losing the Leap Date

For a March 1 policy, adjust each candidate year separately. Build both this year’s and next year’s anniversary from the original date. Then add one day only when a February 29 birth has landed on February 28. This keeps a future leap year from inheriting a previous adjustment.

The following example stands alone. Its fixed business date deliberately points at a non-leap year. Change that variable to a leap-year date and inspect the calculated anniversary. The predicate still uses the same half-open date window.

Do not add a day to every February birthday. The condition checks the original month and day together. It also checks the candidate day, so a valid February 29 stays unchanged. Keep this calculation in one shared query or view if several reminder lists need it. Separate copies tend to develop separate birthdays for the same person.

DECLARE @today date = '20250220';
DECLARE @birth_date date = '20000229';
WITH YearCandidates AS
(
    SELECT DATEADD(year, DATEDIFF(year, @birth_date, @today),
        @birth_date) AS ThisYearDate,
        DATEADD(year, DATEDIFF(year, @birth_date, @today) + 1,
        @birth_date) AS NextYearDate
), PolicyDates AS
(
    SELECT DATEADD(day,
        CASE WHEN MONTH(@birth_date) = 2 AND DAY(@birth_date) = 29
                  AND DAY(ThisYearDate) = 28 THEN 1 ELSE 0 END,
        ThisYearDate) AS ThisYearDate,
        DATEADD(day,
        CASE WHEN MONTH(@birth_date) = 2 AND DAY(@birth_date) = 29
                  AND DAY(NextYearDate) = 28 THEN 1 ELSE 0 END,
        NextYearDate) AS NextYearDate
    FROM YearCandidates
), Chosen AS
(
    SELECT CASE WHEN ThisYearDate < @today THEN NextYearDate
                ELSE ThisYearDate END AS NextBirthday
    FROM PolicyDates
)
SELECT NextBirthday
FROM Chosen
WHERE NextBirthday >= @today
  AND NextBirthday < DATEADD(day, 30, @today);

Why Month and Day Filters Break

A condition that asks for months between December and January has no ordinary ascending range. December is 12 and January is 1. A separate day filter makes things worse because day numbers restart every month. Those values describe parts of a date, not a continuous calendar window.

The same problem appears away from New Year’s Day. A range beginning late in one month crosses into low day numbers in the next. Comparing full next-occurrence dates handles both cases with one predicate. February’s length then comes from SQL Server’s date arithmetic.

For upcoming birthdays, calculate first and filter second. Keep the range condition readable enough to review without a whiteboard. If an existing report uses a long collection of OR conditions for each month, compare it against this date-based approach. Focus on people near the boundary, rather than checking only a familiar birthday in the middle.

Test Upcoming Birthdays at the Edges

Use deliberate test inputs instead of waiting for December to arrive. Include a birthday today, one yesterday, one at the excluded upper boundary, and a January birthday viewed from December. Add February 29 with both target-year types. Sort by the calculated date and then by a stable identifier.

This next query exposes the calculated date before applying a reminder filter. That makes it useful for reviewing the February 28 policy. The yesterday case moves to the next year, and the upper boundary case lands exactly on the excluded date. The labels describe inputs, not production results.

Check the data too. A missing birth date should stay missing, rather than receiving an invented anniversary. A future birth date needs correction or deliberate exclusion. Avoid displaying a birth year when the reminder needs only a name and next date. Access to personal information should match the purpose of the report, even when the report feels harmless.

WITH TestCases AS
(
    SELECT * FROM (VALUES
    ('Year end', CONVERT(date, '19900105'), CONVERT(date, '20231220')),
    ('Today', CONVERT(date, '19901220'), CONVERT(date, '20231220')),
    ('Yesterday', CONVERT(date, '19901219'), CONVERT(date, '20231220')),
    ('Upper boundary', CONVERT(date, '19900119'), CONVERT(date, '20231220')),
    ('Non-leap', CONVERT(date, '20000229'), CONVERT(date, '20250220')),
    ('Next leap year', CONVERT(date, '20000229'), CONVERT(date, '20230301'))
    ) AS v(CaseLabel, BirthDate, TodayDate)
), Candidates AS
(
    SELECT *, DATEADD(year, DATEDIFF(year, BirthDate, TodayDate),
        BirthDate) AS ThisYearDate
    FROM TestCases
)
SELECT CaseLabel, TodayDate,
    CASE WHEN ThisYearDate < TodayDate
         THEN DATEADD(year, DATEDIFF(year, BirthDate, TodayDate) + 1,
              BirthDate)
         ELSE ThisYearDate END AS NextBirthday
FROM Candidates
ORDER BY CaseLabel;

Keep Upcoming Birthdays Predictable

A daily job should receive one business date and use it throughout the run. Do not call the clock repeatedly while building different portions of the same list. Save the chosen policy with the query’s documentation so a later change does not look like a mysterious data issue.

For a small directory, this calculation is straightforward. For a large source, inspect the execution plan and the work required to compute candidates. An ordinary index on the birth date does not turn this year-adjusted expression into a simple seek. A maintained anniversary calendar is a separate design choice, justified by actual workload evidence.

Your final check is practical: can you explain why each included person appears and why the next excluded person does not? Upcoming birthdays should follow the same rule every day. Once the window and leap-day convention are explicit, the report becomes easy to trust and easy to maintain.

Related reading on this blog: Trivia: Days in a Year and SQL Authority 15 Years of Blogging and Upcoming Changes.

A birthday reminder is not a month comparison, it is a search for the next calendar occurrence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.