How to calculate age from date of birth in SQL

Last updated on September 21st, 2021 at 11:11 am

Here’s how to calculate age from date of birth in SQL. You can use the following MySQL query. Just replace date_of_activity and table_name with your column name. date_of_activity is a column in table table_name. This is useful if you need to find out recency of activity.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(date_of_activity)), '%Y') + 0 from table_name;

with stored variable

SET @date_of_activity='2013-29-11 10:00:00';
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@date_of_activity)), '%Y') + 0 from table_name;

The above statement subtracts the date of activity from latest date. It then converts the result into years.

Some use cases:

Calculate age from date of birth of person in years

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(date_of_birth)), '%Y') + 0 from table_name;

Calculate comment’s age from date of posting a comment

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(date_of_posting)), '%Y') + 0 from table_name;

Calculate user’s age from date of joining or signup

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(date_joined)), '%Y') + 0 from table_name;