MySQL Cookbook

Calculating Ages

Problem

You want to know how old someone is.

Solution

This is a problem of computing the interval between dates, but with a twist. For an age in years, it’s necessary to account for the relative placement of the start and end dates within the calendar year. For an age in months, it’s also necessary to account for the placement of the months and the days within the month.

Discussion

Age determination is a type of date interval calculation, but one that cannot be done by computing a difference in days and dividing by 365. That doesn’t work because leap years throw off the calculation. (The interval from 1995-03-01 to 1996-02-29 spans 365 days, but is not a year in age terms.) Using 365.25 is slightly more accurate, but still not correct for all dates. Instead, it’s necessary to determine the difference between dates in years and then adjust for the relative location of the dates within the calendar year. (Suppose Gretchen Smith was born on April 14, 1942. To compute how old Gretchen is now, we must account for where the current date falls within the calendar year: she’s one age up through April 13 of the year, and one year older from April 14 through the end of the year.) This section shows how to perform this kind of calculation to determine ages in units of years or months.

Determining Ages in Years

In general, given a birth date birth, an age in years on a target date d can be computed by the following logic:

if (d occurs earlier in the year than birth) age = YEAR(d) ...