Calculating Age From a Date of Birth Correctly in T-SQL

January arrives and a year-boundary calculation gives everyone another birthday. Calculating age requires checking whether the birthday has actually occurred on the chosen date. Decide the February 29 rule as well, because a calendar function cannot choose your policy for you.

Reproduce the Year-Boundary Trap

DATEDIFF counts boundaries of the requested date part. With YEAR, it counts crossed year numbers. It does not count completed birthdays. That distinction matters for eligibility, discounts, and reports that place people into age bands. Use a supplied as-of date so the example remains reproducible tomorrow.

DECLARE @birth date='20100228',@as_of date='20250227';
SELECT DATEDIFF(year,@birth,@as_of) AS boundary_count,
       DATEDIFF(year,@birth,@as_of)
       - CASE WHEN DATEADD(year,DATEDIFF(year,@birth,@as_of),@birth)>@as_of
              THEN 1 ELSE 0 END AS completed_years;

I test the day before, the birthday, and the day after. Those three dates catch a surprising number of report mistakes. Which date should the report use: today, enrollment, or the transaction date? A query that uses today's date everywhere changes historical answers whenever it runs.

Calculating Age From Completed Anniversaries

Start with the year difference, construct that many anniversaries from the birth date, and subtract one if the anniversary lies after the as-of date. Use date inputs when time of day is irrelevant. The formula below also rejects future birth dates instead of producing a negative age without explanation.

CREATE TABLE #Person
(
    PersonID int PRIMARY KEY,
    DateOfBirth date NOT NULL,
    BirthMonthDay AS
      (DATEPART(month,DateOfBirth)*100+DATEPART(day,DateOfBirth)) PERSISTED
);
INSERT #Person(PersonID,DateOfBirth)
VALUES(1,'20100228'),(2,'20120229'),(3,'19991231'),(4,'20000301');
DECLARE @as_of date='20250228';
SELECT PersonID,DateOfBirth,
       CASE WHEN DateOfBirth>@as_of THEN NULL ELSE
         DATEDIFF(year,DateOfBirth,@as_of)
         - CASE WHEN DATEADD(year,DATEDIFF(year,DateOfBirth,@as_of),DateOfBirth)>@as_of
                THEN 1 ELSE 0 END
       END AS completed_years
FROM #Person;

Run subsequent examples in the same session. The persisted computed column needs QUOTED_IDENTIFIER on, which SSMS sets by default; with sqlcmd, add the -I switch. A permanent table should have an input-validation rule appropriate to the application. I separate invalid data from a valid zero-year age. They need different messages and different correction paths.

State the February 29 Policy

DATEADD shifts February 29 to February 28 when adding years into a nonleap year. The preceding formula therefore uses a February 28 anniversary policy. Some organizations use March 1 instead. Legal and business definitions vary, so record the chosen rule rather than claiming one universal answer.

For a March 1 policy, calculate the anniversary in the as-of year explicitly. Use DATEFROMPARTS with month and day after substituting March 1 for a leap-day birth in a nonleap year. Determine leap behavior from February's last day. Do not create an invalid February 29 date first and hope a later CASE will rescue it.

DECLARE @as_of date='20250228',@birth date='20120229';
DECLARE @leap bit=CASE WHEN DAY(EOMONTH(DATEFROMPARTS(YEAR(@as_of),2,1)))=29
                      THEN 1 ELSE 0 END;
DECLARE @anniversary date=DATEFROMPARTS(YEAR(@as_of),
    CASE WHEN MONTH(@birth)=2 AND DAY(@birth)=29 AND @leap=0 THEN 3 ELSE MONTH(@birth) END,
    CASE WHEN MONTH(@birth)=2 AND DAY(@birth)=29 AND @leap=0 THEN 1 ELSE DAY(@birth) END);
SELECT DATEDIFF(year,@birth,@as_of)
       - CASE WHEN @anniversary>@as_of THEN 1 ELSE 0 END AS march_first_policy_age;

Calculating Age on a Historical Date

Keep the as-of date as a parameter throughout the calculation. An application server and database server can disagree around midnight or across time zones. Select the business date once and pass it into the query. Avoid calling several current-time functions inside one larger calculation.

I put the chosen date beside the age during review. It makes the result auditable without guessing which clock produced it. For a historical export, store that as-of date with the export metadata. Birth date alone is insufficient to reproduce an age that was calculated months ago.

Break the Interval Into Years, Months and Days

Completed years are one answer. A human-readable calendar interval needs a sequence: full years, then full months after that anniversary, then remaining days. Month lengths differ, so dividing days by 365 or 30 does not express calendar age. The following query uses the February 28 policy and DATEADD's month-clamping behavior.

DECLARE @as_of date='20250926';
SELECT p.PersonID,y.full_years,m.full_months,
       DATEDIFF(day,DATEADD(month,m.full_months,a.year_anchor),@as_of) AS remaining_days
FROM #Person AS p
CROSS APPLY (VALUES
 (DATEDIFF(year,p.DateOfBirth,@as_of)-CASE
   WHEN DATEADD(year,DATEDIFF(year,p.DateOfBirth,@as_of),p.DateOfBirth)>@as_of
   THEN 1 ELSE 0 END)) AS y(full_years)
CROSS APPLY (VALUES(DATEADD(year,y.full_years,p.DateOfBirth))) AS a(year_anchor)
CROSS APPLY (VALUES
 (DATEDIFF(month,a.year_anchor,@as_of)-CASE
   WHEN DATEADD(month,DATEDIFF(month,a.year_anchor,@as_of),a.year_anchor)>@as_of
   THEN 1 ELSE 0 END)) AS m(full_months)
WHERE p.DateOfBirth<=@as_of;

Calendar decompositions need an explicit rule at month ends. Test births near January 31 and February 29. The output describes the selected calculation order; it is not an interchangeable elapsed-days measure. For medical or scientific work, use the unit that the actual specification requires.

Index the Recurring Birthday Key

Finding birthdays next week is different from finding ages today. Applying MONTH and DAY to every stored birth date at query time can force a scan. Store or compute an indexed month-day key instead. Generate the seven actual upcoming dates, then join their keys to the indexed column. That also handles December-to-January boundaries naturally.

CREATE INDEX IX_Person_Birthday ON #Person(BirthMonthDay);
DECLARE @start date='20250226';
WITH dates AS
(
    SELECT DATEADD(day,v.n,@start) AS birthday_date
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6)) AS v(n)
)
SELECT p.PersonID,d.birthday_date
FROM dates AS d
JOIN #Person AS p
  ON p.BirthMonthDay=MONTH(d.birthday_date)*100+DAY(d.birthday_date)
UNION ALL
SELECT p.PersonID,d.birthday_date
FROM dates AS d
JOIN #Person AS p ON p.BirthMonthDay=229
WHERE MONTH(d.birthday_date)=2 AND DAY(d.birthday_date)=28
  AND DAY(EOMONTH(d.birthday_date))=28;

Align Birthday Search With the Age Rule

The extra UNION ALL branch observes February 29 birthdays on February 28 in nonleap years. Remove or replace that branch for a different policy. Do not let the birthday reminder and the age calculation use different rules. Inspect the actual plan and logical reads on realistic data; a tiny fixture does not prove index selection.

I test all seven dates, including a year boundary and both leap and nonleap February. Keep future birth dates out of notifications if the application allows provisional records. A calendar gives you dates. It does not attend the policy meeting.

Keep the Inputs for Calculating Age Visible

Do not store a calculated age as the only source value. It becomes stale without any row update. Keep the birth date and calculate against the requested date. If a report materializes ages for a specific run, store that run's as-of date too. Otherwise nobody can tell whether a value was correct when produced.

Avoid using a year count as a filter when an indexed birth-date range expresses the actual rule. For ordinary nonleap cases, a date cutoff can support a seek. Boundary rules for February 29 still need the same policy as the display formula. Test eligibility separately from displayed age. A correct label does not prove that every row entered the right age band.

For the years-months-days calculation, add the reported years to the birth date. Add its months to that anchor, then add its days. Under the chosen clamping rule, the reconstructed date should equal the as-of date. That is a useful invariant to test. Also check that no component becomes negative for valid inputs. I keep these checks with the leap-day and month-end fixtures.

Store birth dates as date values, not formatted strings. Define treatment of unknown dates, future dates, and leap-day anniversaries. Test completed ages against hand-checked boundary cases before putting them into eligibility logic.

I keep a short matrix with ordinary birthdays, leap birthdays, month ends, and the selected as-of date. Calculating age correctly begins with a clear definition of age. Once that definition is explicit, the SQL can be checked instead of argued about.

Related reading on this blog: DATEDIFF: Accuracy of Various Dateparts and Detecting Leap Year in T-SQL using SQL Server 2012: IIF, EOMONTH and CONCAT Function.

Age is not a count of year boundaries, it is the number of completed birthdays under a stated calendar rule.

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