Calculate Age from given Date of Birth in MySQL?

To calculate age in MySQL from Date of Birth, you can use the following syntax −

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(yourColumnName)
- (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(yourColumnName, 5)) as anyVariableName from yourTableName;

To understand the above concept, let us create a table. The following is the query to create a table.

mysql> create table AgeCalculatesDemo
   −> (
   −> YourDateOfBirth datetime
   −> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table with the help of insert command. These records are the DOBs through which we will calculate the age. The following is the query −

mysql> insert into AgeCalculatesDemo values(date_add(now(),interval 22 year));
Query OK, 1 row affected (0.23 sec)

mysql> truncate table AgeCalculatesDemo;
Query OK, 0 rows affected (0.89 sec)

mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -22 year));
Query OK, 1 row affected (0.16 sec)

mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -12 year));
Query OK, 1 row affected (0.18 sec)

mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -19 year));
Query OK, 1 row affected (0.15 sec)

mysql> insert into AgeCalculatesDemo values('2010-4-05');
Query OK, 1 row affected (0.25 sec)

Now you can display all records with the help of select statement. The following is the query −

mysql> select *from AgeCalculatesDemo;

The following is the output −

+---------------------+
| YourDateOfBirth     |
+---------------------+
| 1996-12-06 23:35:26 |
| 2006-12-06 23:35:32 |
| 1999-12-06 23:35:42 |
| 2010-04-05 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

Here is the query that can be used to calculate age.

mysql> SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(YourDateOfBirth)
   −> - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(YourDateOfBirth, 5)) as YourAge
   −> from AgeCalculatesDemo;

The following is the output displaying age −

+---------+
| YourAge |
+---------+
|      22 |
|      12 |
|      19 |
|       8 |
+---------+
4 rows in set (0.00 sec)