Get difference in years between two dates in MySQL as an integer
I am trying to calculate how old is a person in a database.
Let's suppose to have this simple table:
student(id, birth_date);
Where id is the primary key (truly the table is more complex but I have simplified it).
I want to get how much old is a single person:
select id, datediff(curdate(),birth_date)
from student
But it returns a result in days, and not in years.I could divide it by 365:
select id, datediff(curdate(),birth_date) / 365
from student
But it returns a floating point value, and I want an integer.
So I could calculate the years:
select id, year(curdate())-year(birth_date)
from student
But there is a problem: for instance now is May, if a person war born in June, 1970 he has still 31 years and not 32, but the expression returns 32.
I can't come out of this problem, can someone help me?
For anyone who comes across this:
another way this can be done is:
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student
For differences in months, replace YEAR
with MONTH
, and for days replace YEAR
with DAY
Hope that helps!
select id, floor(datediff(curdate(),birth_date) / 365)
from student
What about flooring the result to be an integer?
Try:
SELECT
DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_date)), '%Y')+0
AS age FROM student;