Calculate Age in MySQL (Innodb)

Calculate Age in MySQL (InnoDb)

If the value is stored as a DATETIME data type:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age 
  FROM YOUR_TABLE

Less precise when you consider leap years:

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
  FROM YOUR_TABLE t 

How to calculate age based on date of birth in mysql?

You can use TIMESTAMPDIFF.

select TIMESTAMPDIFF(YEAR, str_to_date(dob, '%Y-%M-%d'), current_date) AS age
from user;

Calculate age with decimals from date of birth

Usually DOB calculation is pretty easy in mysql when you want to calculate the years without any fraction something as

mysql> select timestampdiff(YEAR,'1981-06-01',now());
+----------------------------------------+
| timestampdiff(YEAR,'1981-06-01',now()) |
+----------------------------------------+
|                                     33 |
+----------------------------------------+

But since you need the fraction also then this should do the trick

mysql> select format(datediff(curdate(),'1981-06-01') / 365.25,2);
+-----------------------------------------------------+
| format(datediff(curdate(),'1981-06-01') / 365.25,2) |
+-----------------------------------------------------+
| 33.02                                               |
+-----------------------------------------------------+

Year is considered as 365.25 days.

So in your case you may have the query as

select 
format(datediff(curdate(),dob) / 365.25,2) as dob 
from players limit 5;

How to Determine the Age of a Person in aTable

I believe something like this should work:

SELECT * 
FROM patientdetails 
WHERE DATEDIFF(CURRENT_DATE, STR_TO_DATE(p.DOB, '%d-%m-%Y'))/365 < 19

SQL. Convert age to date of birth

In MySQL 8.x you can use INTERVAL arithmetic. For example:

select current_date() - interval 42 year 

Result:

1978-05-21

In your case this could translate to:

select current_date() - interval age year from users;

See running example at DB Fiddle.

---

Related Topics