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;
![mm](https://i0.wp.com/ubiq.co/database-blog/wp-content/uploads/2024/05/db908c05ad732bd197c1eaad1c6675d2.jpeg?resize=150%2C150&ssl=1)
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.