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;
Related posts:
How to Setup Rsyslog with MySQL
How to Get Row Count For All Tables in MySQL
How to Enable SSL for MySQL in Windows
How to Lock User Account in MySQL
How to Copy/Transfer Data from One Database to Another in MySQL
How to Run MySQLdump without Locking Tables
MySQL Datetime vs Timestamp
How to Get Digits from String in MySQL