Building an age calculator in SQL is mostly a date-arithmetic problem: you have a date of birth (DOB), an “as of” date, and you need to return the person’s completed age. The important detail is that SQL is implemented by different database systems, so the best expression depends on whether you use PostgreSQL, MySQL, SQL Server, or SQLite.
Quick answer: age calculator in SQL
For a completed age in years, do not simply subtract the birth year from the current year. That can be one year too high before the birthday. Instead, use a database function or expression that accounts for the birthday.
| Database | Completed-age approach | Useful date function |
|---|---|---|
| PostgreSQL | EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)) | AGE() |
| MySQL | TIMESTAMPDIFF(YEAR, dob, CURDATE()) | TIMESTAMPDIFF() |
| SQL Server | DATEDIFF plus a birthday check | DATEDIFF(), DATEADD() |
| SQLite | strftime() year/month/day comparison | strftime() |
PostgreSQL is especially convenient because its age() function returns a symbolic interval containing years and months rather than only a raw number of days. MySQL provides TIMESTAMPDIFF() for differences in a chosen unit. SQL Server’s DATEDIFF() counts datepart boundaries, so a raw DATEDIFF(year, dob, today) should not be treated as completed age without a birthday adjustment. SQLite has a smaller built-in date/time API, so its year calculation is commonly written as a text comparison.
How SQL should calculate age from DOB
Suppose a table contains date_of_birth. A correct age calculation compares that DOB with a reference date. The basic business rule is:
- Start with the difference between the reference year and birth year.
- Check whether the birthday has occurred in the reference year.
- Subtract one if the birthday has not occurred yet.
- For years-months-days output, use calendar-aware date functions rather than converting everything to an average number of days.
Using a fixed as-of date is usually better for reports and historical data than always using today. It makes the query reproducible and lets you answer questions such as “What was this customer’s age on 1 January 2026?”
PostgreSQL age calculator
PostgreSQL has a dedicated age() function. The PostgreSQL documentation describes age(timestamp, timestamp) as producing a symbolic result in years and months, and age(timestamp) as comparing a timestamp with the current date. That makes PostgreSQL a natural fit for calendar age calculations.
SELECT
date_of_birth,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth))::int AS age
FROM people
WHERE date_of_birth IS NOT NULL;The result is the number of completed years. For an explicit reference date, replace CURRENT_DATE with a date or parameter:
SELECT
date_of_birth,
EXTRACT(
YEAR FROM AGE(DATE '2026-09-21', date_of_birth)
)::int AS age_on_date
FROM people
WHERE date_of_birth IS NOT NULL;PostgreSQL years, months and days
If you need an exact calendar breakdown instead of only completed years, return the interval itself:
SELECT
date_of_birth,
AGE(DATE '2026-09-21', date_of_birth) AS age_interval
FROM people
WHERE date_of_birth IS NOT NULL;You can also extract each component:
SELECT
EXTRACT(YEAR FROM AGE(DATE '2026-09-21', date_of_birth))::int AS years,
EXTRACT(MONTH FROM AGE(DATE '2026-09-21', date_of_birth))::int AS months,
EXTRACT(DAY FROM AGE(DATE '2026-09-21', date_of_birth))::int AS days
FROM people
WHERE date_of_birth IS NOT NULL;
MySQL age calculator
MySQL provides CURDATE() for the current date and TIMESTAMPDIFF() for the difference between two temporal values in a selected unit. For completed years, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) is a concise solution.
SELECT
date_of_birth,
TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age
FROM people
WHERE date_of_birth IS NOT NULL;For a fixed reference date, pass that date instead of CURDATE():
SELECT
date_of_birth,
TIMESTAMPDIFF(
YEAR,
date_of_birth,
'2026-09-21'
) AS age_on_date
FROM people
WHERE date_of_birth IS NOT NULL;For total elapsed days, MySQL’s DATEDIFF() works with the date parts of its arguments:
SELECT
date_of_birth,
DATEDIFF(CURDATE(), date_of_birth) AS total_days
FROM people
WHERE date_of_birth IS NOT NULL;SQL Server age calculator
SQL Server needs a little more care. Microsoft documents DATEDIFF() as counting the specified datepart boundaries crossed between two dates. That means DATEDIFF(year, date_of_birth, GETDATE()) counts New Year boundaries, not birthdays. A birthday adjustment is therefore needed for completed age.
SELECT
date_of_birth,
DATEDIFF(year, date_of_birth, CAST(GETDATE() AS date))
- CASE
WHEN DATEADD(
year,
DATEDIFF(year, date_of_birth, CAST(GETDATE() AS date)),
date_of_birth
) > CAST(GETDATE() AS date)
THEN 1
ELSE 0
END AS age
FROM people
WHERE date_of_birth IS NOT NULL;The same pattern can use a fixed as-of date. This is useful for an eligibility report or historical snapshot.
DECLARE @as_of date = '2026-09-21';
SELECT
date_of_birth,
DATEDIFF(year, date_of_birth, @as_of)
- CASE
WHEN DATEADD(
year,
DATEDIFF(year, date_of_birth, @as_of),
date_of_birth
) > @as_of
THEN 1
ELSE 0
END AS age_on_date
FROM people
WHERE date_of_birth IS NOT NULL;Why SQL Server needs the birthday check
Consider a person born on 15 December 2000 and an as-of date of 1 October 2026. A raw year difference is 26 because the calculation crosses the 2025-to-2026 year boundary, but the person has not reached the 15 December birthday yet. The completed age is therefore 25. The DATEADD() comparison catches that case.
SQLite age calculator
SQLite’s date/time functions use text representations and modifiers rather than a dedicated AGE() function. For a DOB stored as ISO-style YYYY-MM-DD, a compact completed-age expression compares the month/day portion of the DOB with today’s month/day.
SELECT
date_of_birth,
CAST(strftime('%Y', 'now') AS INTEGER)
- CAST(strftime('%Y', date_of_birth) AS INTEGER)
- (
strftime('%m-%d', 'now') < strftime('%m-%d', date_of_birth)
) AS age
FROM people
WHERE date_of_birth IS NOT NULL;SQLite stores dates in flexible forms, so this expression assumes the DOB values are consistently stored in a format that strftime() can parse, such as YYYY-MM-DD. Validate or normalize imported data before relying on it in production.
SQL age calculator with an as-of date
An as-of date makes age calculations deterministic. It is especially useful when the same query is run later and you need the result to remain tied to a business event, report date, application deadline, or historical snapshot.
| Use case | Reference date | Why it matters |
|---|---|---|
| Current age | Today’s date | Changes each day |
| Recruitment eligibility | Notification cutoff date | Matches the official rule |
| Historical report | Report date | Keeps results reproducible |
| Birthday calculation | Target date | Supports future or past age |
How to calculate exact age in years, months and days
“Age in years” and “elapsed days” are different measurements. A calendar age such as 25 years, 3 months and 6 days cannot be reconstructed reliably by dividing a day count by 365 because leap years and month lengths vary.
| Output | Recommended method |
|---|---|
| Completed years | Calendar birthday logic or a database’s year-aware function |
| Years, months, days | PostgreSQL AGE() or equivalent calendar-aware logic |
| Total days | Date subtraction such as MySQL DATEDIFF() |
| Historical age | Use an explicit as-of date |
If your application needs an exact years-months-days result in MySQL, SQL Server, or SQLite, define the calendar policy explicitly and test boundary dates. Do not silently substitute an average-year calculation for a calendar age.
Leap years and February 29 birthdays
Leap-day birthdays are an important edge case. A person born on 29 February has a valid birthday only in leap years, but their completed age still advances according to the calendar-age policy used by the application. Database functions can handle some of the date arithmetic, but your product or legal requirement may define how a 29 February birthday is treated in a non-leap year.
For eligibility systems, use the rule in the relevant policy or notification rather than inventing a leap-day convention. For ordinary age displays, document the convention used by your application and cover it with tests.
Validate the DOB before calculating age
A good SQL age calculator should not treat every value in the DOB column as trustworthy. At minimum, consider null values, future birth dates, malformed imported dates, and unexpected time components.
-- Example validation idea
SELECT *
FROM people
WHERE date_of_birth IS NULL
OR date_of_birth > CURRENT_DATE;The exact syntax for “today” differs by database. More importantly, validation should happen at the data-entry boundary when possible, not only inside reporting queries.
Age calculator SQL examples with a people table
Suppose your table is:
CREATE TABLE people (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
date_of_birth DATE
);For production systems, choose the date type recommended by your database and schema. A dedicated date value is generally preferable when the business concept is a birthday rather than a timestamp of an event.
| Requirement | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Current date | CURRENT_DATE | CURDATE() | CAST(GETDATE() AS date) |
| Calendar age | AGE() | TIMESTAMPDIFF(YEAR,...) | Birthday-adjusted DATEDIFF() |
| Elapsed days | Date subtraction | DATEDIFF() | DATEDIFF(day,...) |
Common SQL age-calculation mistakes
- Subtracting years only:
YEAR(today) - YEAR(dob)ignores whether the birthday has happened. - Using SQL Server DATEDIFF(year) as age: it counts year boundaries, not completed birthdays.
- Dividing days by 365: leap years make this unsuitable for calendar age.
- Ignoring null and future DOBs: bad input can create misleading reports.
- Mixing date and timestamp semantics: a birthday is often a date, not a moment in time.
- Hard-coding today’s date: use a parameterized as-of date when reproducibility matters.
- Assuming every SQL database has the same functions: SQL syntax is dialect-specific.
How to test an SQL age calculator
Use boundary-focused test data rather than checking only a few ordinary birthdays. A useful test set includes birthdays today, tomorrow, yesterday, the last day of a month, 28 February, 29 February, and dates around New Year’s Day.
| Test | What to verify |
|---|---|
| Birthday today | Age has already incremented |
| Birthday tomorrow | Age has not incremented |
| Birthday yesterday | Age has incremented |
| 29 February DOB | Leap-year policy is consistent |
| Future DOB | Validation rejects or flags it |
| Null DOB | Query does not report a misleading age |
Which SQL approach should you use?
Use the date functions native to your database instead of forcing one SQL dialect into another. PostgreSQL’s AGE() is useful when you want a calendar-aware interval. MySQL’s TIMESTAMPDIFF() provides a compact completed-year calculation. SQL Server requires particular care because DATEDIFF(year,...) counts datepart boundaries. SQLite can calculate age with strftime() when dates are stored consistently.
If the SQL query feeds an application, keep the reference date explicit when the result must be reproducible. If it feeds an eligibility decision, use the exact cutoff and age rule specified by the governing requirement.
FAQ: age calculator in SQL
How do I calculate age from DOB in SQL?
Use a calendar-aware age expression for your database. PostgreSQL can use EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth)); MySQL can use TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()). SQL Server needs a birthday adjustment around DATEDIFF().
Why is YEAR(today) – YEAR(birthdate) sometimes wrong?
Because the calculation ignores the month and day. If the birthday has not occurred yet in the current year, the completed age is one less than the simple year difference.
What is the best SQL function for age?
It depends on the database. PostgreSQL has AGE(), MySQL has TIMESTAMPDIFF(), SQL Server provides DATEDIFF() and DATEADD(), and SQLite uses its date/time functions such as strftime().
Can SQL calculate age on a specific date?
Yes. Replace the current-date expression with a parameter or literal as-of date. This is recommended for historical reports, eligibility cutoffs, and repeatable tests.
Can SQL calculate age in years, months and days?
Yes, but the implementation is database-specific. PostgreSQL’s AGE() directly returns a calendar-aware interval. Other databases require their own date arithmetic and a clearly defined policy for month lengths and leap-day birthdays.
Related age-calculator programming guides
If you are implementing the same DOB logic outside SQL, see our guides for age calculator in Python, age calculator in PHP, age calculator in Java, and age calculator in JavaScript.
Sources and documentation
- PostgreSQL 18: Date/Time Functions and Operators
- MySQL 8.4: Date and Time Functions
- Microsoft Learn: DATEDIFF (Transact-SQL)
- Microsoft Learn: DATEADD (Transact-SQL)
Bottom line: an SQL age calculator should calculate completed birthdays, not merely subtract calendar years. Choose the expression for your database, use an explicit as-of date when reproducibility matters, validate DOB data, and test birthday and leap-year boundaries.
