How to get an age from a D.O.B field in MySQL?

MySQLMySQLi Database

To get age from a D.O.B field in MySQL, you can use the following syntax. Here, we subtract the DOB from the current date.

select yourColumnName1,yourColumnName2,........N,year(curdate())-
   year(yourDOBColumnName) as anyVariableName from yourTableName;

To understand the above syntax, let us first create a table. The query to create a table is as follows.

mysql> create table AgeDemo
-> (
-> StudentId int,
-> StudentName varchar(100),
-> StudentDOB date
-> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command. The query is as follows.

mysql> insert into AgeDemo values(1,'John','1998-10-1');
Query OK, 1 row affected (0.20 sec)

mysql> insert into AgeDemo values(2,'Carol','1990-1-2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into AgeDemo values(3,'Sam','2000-12-1');
Query OK, 1 row affected (0.15 sec)

mysql> insert into AgeDemo values(4,'Mike','2010-10-11');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from AgeDemo;

The following is the output.

+-----------+-------------+------------+
| StudentId | StudentName | StudentDOB |
+-----------+-------------+------------+
| 1         | John        | 1998-10-01 |
| 2         | Carol       | 1990-01-02 |
| 3         | Sam         | 2000-12-01 |
| 4         | Mike        | 2010-10-11 |
+-----------+-------------+------------+
4 rows in set (0.00 sec)

Here is the query to calculate the age from D.O.B. The query is as follows.

mysql> select StudentName,StudentDOB,year(curdate())-year(StudentDOB) as StudentAge from AgeDemo;

The following is the output displaying age.

+-------------+------------+------------+
| StudentName | StudentDOB | StudentAge |
+-------------+------------+------------+
| John        | 1998-10-01 | 21         |
| Carol       | 1990-01-02 | 29         |
| Sam         | 2000-12-01 | 19         |
| Mike        | 2010-10-11 | 9          |
+-------------+------------+------------+
4 rows in set (0.03 sec)
raja
Published on 11-Jan-2019 11:13:09
Advertisements