Syntax error Calculate age based on date of birth in MySQL?

Calculate age based on date of birth in MySQL?

Calculate Age based on date of birth with the help of DATE_FORMAT() method in MySQL. Firstly, get the current date time with the help of now() method and you can place your date of birth in DATE_FORMAT().

The syntax is as follows −

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),'yourDateofbirth')), '%Y')+0 AS anyVariableName;

Apply the above syntax to calculate age from yourDateofbirth. In the above syntax, replace yourDateofbirth with your date of birth. The query is as follows −

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),'2010-11-25')), '%Y')+0 AS Age;

The following is the output −

+------+
| Age  |
+------+
|    8 |
+------+
1 row in set (0.00 sec)

Let us now see this in an example. Firstly, create a table −

mysql> create table AgeCalculationFromDatetime
   -> (
   -> YourDateofBirth datetime
   -> );
Query OK, 0 rows affected (0.52 sec)

Inserting date of birth into table. The query is as follows −

mysql> insert into AgeCalculationFromDatetime values('1995-11-25');
Query OK, 1 row affected (0.13 sec)

Displaying all records with the help of select statement. The query is as follows −

mysql> select *from AgeCalculationFromDatetime;

The following is the output −

+---------------------+
| YourDateofBirth     |
+---------------------+
| 1995-11-25 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

The query to calculate age is as follows −

mysql> SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),YourDateofBirth)), '%Y')+0 AS Age from AgeCalculationFromDatetime;

Here is the output −

+------+
| Age  |
+------+
| 23   |
+------+
1 row in set (0.00 sec)`
Updated on: 2019-07-30T22:30:24+05:30

12K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements