How can we find the employees from MySQL table whose age is greater than say 30 years, providing the only date of birth on the table?
To understand this concept, we are using the data from table ‘emp_tbl’ as follows −
mysql> Select * from emp_tbl;
+--------+------------+
| Name | DOB |
+--------+------------+
| Gaurav | 1984-01-17 |
| Gaurav | 1990-01-17 |
| Rahul | 1980-05-22 |
| Gurdas | 1981-05-25 |
| Naveen | 1991-04-25 |
| Sohan | 1987-12-26 |
+--------+------------+
6 rows in set (0.00 sec)
mysql> SELECT Name,SYSDATE(),DOB,DATEDIFF(SYSDATE(),DOB)/365 AS AGE from emp_tbl WHERE(DATEDIFF(SYSDATE(), DOB)/365)>30;
+--------+---------------------+------------+---------+
| Name | SYSDATE() | DOB | AGE |
+--------+---------------------+------------+---------+
| Gaurav | 2017-12-26 22:33:24 | 1984-01-17 | 33.9644 |
| Rahul | 2017-12-26 22:33:24 | 1980-05-22 | 37.6219 |
| Gurdas | 2017-12-26 22:33:24 | 1981-05-25 | 36.6137 |
| Sohan | 2017-12-26 22:33:24 | 1987-12-26 | 30.0219 |
+--------+---------------------+------------+---------+
4 rows in set (0.10 sec)
Published on 22-Feb-2018 12:38:00
