How to calculate ages and find upcoming birthdays with Oracle SQL #JoelKallmanDay

SQL is 50 years old this year!

Which brings an interesting question. How do you calculate people’s ages and find their birthdays using SQL?

Two approaches to get someone’s age using Oracle SQL are:

• Use months_between to find the months separating birth and current dates
• Find the difference between birth and current dates to numbers in YYYYMMDD format

These look like:

At this point you may be wondering:

Does it matter which I use? What’s the difference between these methods?

The answers are

• Yes it does matter
• The difference is for people born on 29^th February (aka leaplings). These calculations give different results for their age in non-leap years.

In this post we’ll see why this is and solve these age and birthday-related challenges:

• Find ages in years
• Check if someone is at least N years old
• Find people with birthdays today
• Find people with birthdays in the next N days

These queries will get gnarly. So we’ll also see how making process data-driven simplifies your SQL. Grab the ages and birthdays cheat sheet at the end for a summary of the logic.

If you prefer watching to reading, here’s the recording of the Ask TOM SQL Office Hours session all about ages and birthdays:

But first, back to those leaplings. To see why they need special handling, we need to answer an important question:

When is someone one year older?

For most of us the answer is easy: when the current day and month match the day and month of your birth.

If you’re born on a leap day (29^th February) these only match every four years.

In non-leap years you could celebrate on either 28^th February or 1^st March. Which of these you’re officially one year older varies by country.

If you provide age-sensitive services matching this legal definition matters. Ensure you check what the rules are for the systems you’re building and use the appropriate formulas!

With this in mind, let’s return to our age calculations.

Sidenote

The functions below are based on the current date. Oracle Database provides this via the sysdate function. This returns the date and time of the database’s operating system.

This appears to make testing hard, as it seems you need to wait up to four years to check the results for leap days. One way around this is to swap sysdate for a bind variable. This enables you to pass whichever date you want to test.

If you want to stick with sysdate, Oracle Database has another trick. You can set the fixed_date parameter. The sysdate function returns whatever you’ve set this value to from this point. Using this enables you to test the statements for any date you want.

How to convert birth date to current age

Your age is the number of complete years since your birth. One way to find this is to divide the complete months you’ve been alive by twelve. Then round this down to get the total years.

The months_between function in Oracle Database returns the number of months separating two dates. If both dates are the last day of month, the result is an integer. So this makes leaplings one year older on 28^th Feb in non-leap years:

This is a problem if you want leaplings to be one year older on 1^st March in these years.

To overcome this you can use a different method to calculate ages:

• Convert the current and birth dates to numbers in YYYYMMDD format
• Subtract the birth date number from the current date number
• Divide this difference by 10,000 and round the result down

This works because there is a gap of 10,000 for dates whole years apart. To see this in effect for a leapling born in 2000 we get these formulas around their birthday in 2025:

• 20250228 – 20000229 = 249999 => 24.9999 years
• 20250301 – 20000229 = 250072 => 25.0072 years

Thus they’re only a whole year older on 1^st March, not 28^th February as desired:

How to check if someone is at least N years old

Many systems provide content or services that are age restricted. When building these systems, you need to verify users are old enough to use your app.

To do this, move the age calculation formula into your query’s where clause. For example:

This only returns the people who are old enough to use your system. It excludes those too young from the results. If you want to see everyone, but include a flag to check if they’re old enough you can use a case expression like this:

How to find people with birthdays today

To find people celebrating their birthday today, check the day and month of their birth matches the current day and month.

This method omits those born on 29^th February when it’s not a leap year. To fix this, check the current date is 28^th Feb or 1^st Mar as appropriate for the leaplings.

Which gives a SQL statement like:

Note the above assumes you’re using English NLS settings to do the date conversion. To avoid NLS issues, either use a numeric format (e.g. ddmm) or pass ‘NLS_DATE_LANGUAGE = English‘ for the third parameter of to_char in the check that the birth day and month is 29th Feb.

This gets everyone celebrating today. But what if you want to find birthdays coming soon?

How to find upcoming birthdays in the next seven days

You can find future birthdays by extending the solution above to check the next N days.

To do this:

• Generate a row for each date you want to test
• For each of these return the day and month of the date
• Check if a person’s day and month of birth are in this list of values

Again, this basic method ignores leaplings. To include these, union all the rows above with the value 29-FEB if:

• It’s not a leap year
• The value ‘0229’ is between today and the Nth date in MMDD format.

Which looks like:

Take care when choosing the end date for the range. If you want leaplings’ birthdays to be 28^th Feb in non-leap years, you need the range to end in March. If leaplings will celebrate on 1st March, the range must stop at the end of February.

Mixing checking if these in and between date checks brings a question:

Why couldn’t we use the between method to find all upcoming birthdays?

The problem here is when the date range crosses the year end. Say you want to find all the birthdays from 28^th December to 4^th January. The between expression becomes:

'0229' between '1231' and '0104'

This excludes everyone born in this range!

This is because when you convert these dates to MMDD values, the start of the range (1231) is greater than the end (0104). This makes the condition false, regardless of what the birth date is.

This is also an issue for the method above if the date range is greater than two months. To search for larger date ranges you’ll need to update the query to handle this.

The SQL is getting messy. If you need to support leapling birthdays on 28^th Feb or 1^st Mar depending on their location it’s even further complicated.

The good news is:

There is a way to turn these back into simple SQL statements.

Instead of calculating ages and birthdays when fetching the data, store the values in a table.

Store ages and birthdays

The queries above to calculate ages and find birthdays were getting increasingly complicated. One way to simplify these is to follow the mantra:

Minimize code. Maximize data.

One way to do this is to add an age column to your people table:

Then set it using the appropriate method. Finding someone’s age is simply a matter of selecting this column.

This works well for basic use cases. There are pitfalls to be aware of though:

• You need to create a daily job to update ages for everyone with a birthday today
• While this job runs today’s ages are incorrect. If the update is slow or fails this could be a problem
• You’ve locked in when leaplings age. If you need to support both 28^th Feb or 1^st Mar you need another approach
• It only provides ages today. To find birthdays in the past or future you still need to run calculations.

You can overcome these by creating a table storing pairs of birth and calendar dates.

You can then populate this table with every combination of these values. For each pair of dates, store the corresponding age and whether it’s a birthday.

For people born on 29^th February, set their age and birthday on 28^th Feb or 1^st Mar in non-leap years as appropriate.

If you need to support both 28^th Feb and 1^st Mar as birthdays for leaplings, you can add a column to choose when they celebrate at run time.

This gives a table like:

With this table you can remove all the formulas in the SQL above. The statements become a join with a basic where clause:

While storing these date combinations simplifies your SQL, it does bring a different challenge. How many rows you need to store!

The oldest living people are approaching 120 years on this earth. To store all these birth dates and give yourself leeway to store people not yet born, you need to span around 130 years. That’s approaching 50,000 days.

Storing every combination of these ~50,000 dates is to well over two billion rows. If you hold a flag to choose when leaplings celebrate that doubles to 4-5 billion rows.

This is incredibly large.

You can reduce the data set though.

It’s unlikely you need every birth and calendar date combination. You only need to store the calendar dates you want to find the ages and birthdays for. Often, this is likely a rolling window of dates covering the recent past and future. A period covering one or two years is enough for most cases.

This brings the rows needed down tens of millions. Still large, but much more manageable. Create a job to add new dates and prune unneeded values to keep the data set trim.

Summary

Computing ages and identifying upcoming birthdays is fiddly when you account for people born on 29^th February. The formulas you use to do this depends on whether you define leaplings as a year older on 28^th Feb or 1^st Mar when it’s not a leap year.

If you need to choose between these at run time it gets even more complex.

Storing this information as a column on your person/account table or in a table for each birth and calendar date simplifies your queries.

A table of birth and calendar dates may store huge numbers of rows. You can keep this manageable by storing a sliding window of calendar dates and making a job to maintain this window.

As always, the “best” solution for your app depends on which trade-offs are most acceptable to you.

Here’s a summary cheat sheet of these calculations:

UPDATE 24 Oct 2024: Added video embed of Office Hours recording.