Work Out Age From Date In MySQL

12th September 2008 - 2 minutes read time

Rather than work out how many years have passed since and event, or how old something is, after you get the data from a MySQL database you could use the following query to convert the date on the MySQL side. It works by subtracting the current date from the given date and formatting it in years. Adding the given value to 0 casts the string given by DATE_FORMAT() into an interger.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('2000-07-23 09:20:59')), '%Y') + 0;

This gives the result of 8. You can also pass in just the year, month and day string to the function.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('2000-07-23')), '%Y') + 0;

This gives the result of 38.

The function requires that a properly formatted MySQL date string is found or it will produce an error. The following string used in this function.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS('13/01/1970')), '%Y') + 0;

As well as producing a NULL return value will also give the following error.

Truncated incorrect datetime value: '13/01/1970'

Finally, this query only works with the %Y format string for the year in 4 digits, I have had no success with other date format strings.

Add new comment

The content of this field is kept private and will not be shown publicly.