If you’ve ever tried to calculate someone’s age in SQL Server, you’ve probably run into the same trap most beginners fall into. You call DATEDIFF(), get a number back, and it looks right. But then someone born in December throws the whole thing off by a year.
This happens because DATEDIFF() counts the number of year boundaries crossed between two dates, not the actual number of full years that have passed. It’s a subtle difference, but it matters a lot when you’re dealing with real people’s birthdays.
In this article, we’ll walk through the correct way to calculate age in SQL Server, why the naive approach fails, and a few variations you’ll actually use in production.
The Common Mistake
Here’s what you might write if you aren’t aware of the issue:
SELECT DATEDIFF(YEAR, birth_date, GETDATE()) AS age
FROM customers;
This looks fine until you test it against a birthday that hasn’t happened yet this year. Say someone was born on December 20, 1990. If today’s date is July 12, 2026, this query returns 36. But that person won’t actually turn 36 until December. Right now, they’re still 35.
The problem is that DATEDIFF(YEAR, ...) just subtracts the year part of the two dates. It doesn’t check whether the birthday has actually occurred yet within the current year. So anyone whose birthday falls later in the year than today gets counted as a year older than they really are.
The Correct Way to Calculate Age
To fix this, you need to adjust the result based on whether the birthday has already passed this year. The most common way to do this is with a CASE expression that compares the month and day:
SELECT birth_date,
DATEDIFF(YEAR, birth_date, GETDATE()) -
CASE WHEN (MONTH(birth_date) > MONTH(GETDATE()))
OR (MONTH(birth_date) = MONTH(GETDATE()) AND DAY(birth_date) > DAY(GETDATE()))
THEN 1
ELSE 0
END AS age
FROM customers;
This works by taking the raw year difference and subtracting one whenever the birthday hasn’t happened yet this year. It’s not the prettiest query you’ll ever write, but it’s accurate, and accuracy is the whole point here.
There’s a shorter version some people prefer, using a formatted date comparison instead of separate month and day checks:
SELECT birth_date,
DATEDIFF(YEAR, birth_date, GETDATE()) -
CASE WHEN FORMAT(birth_date, 'MMdd') > FORMAT(GETDATE(), 'MMdd')
THEN 1
ELSE 0
END AS age
FROM customers;
This does the same job with less code, but it might not be suitable in all situations. FORMAT() is slower than plain date functions because it relies on .NET formatting under the hood. For a small table this won’t matter. For millions of rows, it can add up, so it’s worth knowing the tradeoff exists.
Handling DATE vs DATETIME Columns
If your birth date column is stored as DATE, none of the above needs any adjustment. But if it’s a DATETIME or DATETIME2 column, you might have a time component sitting alongside the date, and that can quietly cause problems in comparisons.
It’s usually a good idea to strip the time portion off before doing any date math, just to keep things predictable:
SELECT CAST(birth_date AS DATE) AS birth_date_only,
DATEDIFF(YEAR, CAST(birth_date AS DATE), GETDATE()) -
CASE WHEN FORMAT(birth_date, 'MMdd') > FORMAT(GETDATE(), 'MMdd')
THEN 1
ELSE 0
END AS age
FROM customers;
Casting to DATE removes any hours, minutes, or seconds that might be lurking in there, which keeps your age calculation clean regardless of how the data was originally entered.
Calculating Age as of a Specific Date
Sometimes you don’t want someone’s age today. You want their age as of a particular date, like the start of a policy, an event date, or a report cutoff. The same logic applies, you just swap out GETDATE() for whatever reference date you need:
SELECT birth_date,
DATEDIFF(YEAR, birth_date, reference_date) -
CASE WHEN FORMAT(birth_date, 'MMdd') > FORMAT(reference_date, 'MMdd')
THEN 1
ELSE 0
END AS age_at_reference_date
FROM policy_holders;
This is handy for insurance tables, membership records, or anything where “age” needs to be pinned to a moment in time rather than the current date.
Filtering Records by Age
Once you can calculate age reliably, filtering by it is the next natural step. Say you want everyone who is currently 18 or older.
A tempting but flawed approach is to filter directly on the calculated age column using a HAVING clause or a subquery. That works, but there’s a cleaner way that avoids running the age calculation on every row just to compare it:
SELECT customer_id,
birth_date
FROM customers
WHERE birth_date <= DATEADD(YEAR, -18, GETDATE());
This flips the logic around. Instead of calculating everyone’s age and comparing it to 18, you calculate the cutoff date for turning 18 and compare birth dates against that. It’s faster on large tables because it’s sargable, meaning SQL Server can actually use an index on birth_date if one exists. Wrapping the column in a function, like DATEDIFF(YEAR, birth_date, GETDATE()), blocks index usage and forces a scan.
Finding People Within an Age Range
The same date-cutoff trick works for age ranges too:
SELECT customer_id,
birth_date
FROM customers
WHERE birth_date BETWEEN DATEADD(YEAR, -35, GETDATE()) AND DATEADD(YEAR, -25, GETDATE());
This grabs everyone between 25 and 35 years old, calculated relative to today. You can adjust the numbers for whatever range your report or query needs, and it stays fast even on large customer tables.
- Use
DATEADD()with negative values to build age cutoffs instead of calculating age row by row - Cast
DATETIMEcolumns toDATEbefore comparing if time components might be present - Avoid wrapping indexed date columns in functions inside your
WHEREclause
A Note on Leap Years
People born on February 29 cause a small headache once every four years, but SQL Server handles it better than you’d expect. Since the comparison logic relies on month and day values rather than trying to recreate a literal anniversary, someone born on a leap day will still get bumped to the next age correctly once March 1 rolls around in a non-leap year. You don’t need any special handling for it.
Wrapping Up
Calculating age sounds like it should be a one-line query, and in a way it is, just not the one-line query you might expect. The main thing to remember is that raw year subtraction ignores whether the birthday has actually happened yet, and that small oversight can quietly mess up billing, eligibility checks, or reporting.
Once you build the birthday comparison into your query, or better yet, flip your filters around to use date cutoffs instead of calculated ages, you’ll get accurate results that also perform well at scale.