We calculate our age by the difference in full years between our current date and our date of birth. This number indicates the age at which we finished. The age when we start to get a day is one more than that.
In this post we will learn how to calculate age from date of birth in mysql. We can calculate diferent ways. We must use date functions for this. We will use CurDate() function to learn current date, we’ll use year function to get year of a date and we’ll use TIMESTAMPDIFF function to get difference of two year. We’ll write example queries from the library database.
Example Queries
Example-1: List all students name, surname and age
Method -1
This method is not recommended because it usually produces 1 more value. It does not always produce accurate results.
|
1 |
Select name,surname, birthdate, curdate() as CurrentDate, year(curdate())-year(birthDate) as age from students |
Method-2
This method is the recommended method. It always gives accurate results.
|
1 2 |
Select name,surname,birthdate,getdate() as CurrentDate, TIMESTAMPDIFF(year,birthDate,curdate()) as age from students |
Example-2: List all students name, surname and age sorted by their age
|
1 |
Select name,surname,TIMESTAMPDIFF(YEAR,birthDate,CURDATE()) as age from students order by age |
Example-3: List the name and surname of students whose age 17
|
1 |
Select name,surname from students <br>where TIMESTAMPDIFF(YEAR,birthDate,CURDATE()) = 17 |
Example-4: How old was the student named Alfie Watson when he has got the last book
|
1 |
Select top 1 name,surname, TIMESTAMPDIFF(YEAR,birthDate,CURDATE())<br>from students <br>join borrows on students.studentId = borrows.studentId <br>where name = 'Alfie' and Surname='Watson' <br>order by takenDate desc |
In this post we have learned how to calculate age from date of birth in MYsql.
