In this post we will learn how to calculate age from date of birth in sql. We can calculate diferent ways. We must use date functions for this. We will use getdate() function to learn current date, we’ll use year function to get year of a date or we’ll use datediff function to get difference of two year. We’ll write example queries from the library database. You can download it from the link below.
Example Queries
Example-1: List all students name, surname and age
1 | Select name,surname,year(getdate())-year(birthDate) as age from students |
or
1 | Select name,surname,datediff(YY,birthDate,getdate()) as age from students |
Example-2: List all students name, surname and age sorted by their age
1 | Select name,surname,year(getdate())-year(birthDate) as age from students order by age |
or
1 | Select name,surname,datediff(YY,birthDate,getdate()) as age from students order by age |
Example-3: List the name and surname of students whose age 17
1 2 | Select name,surname from students where (year(getdate())-year(birthDate)) = 17 |
or
1 2 | Select name,surname from students where datediff(YY,birthDate,getdate()) = 17 |
Example-4: How old was the student named Alfie Watson when he has got the last book
1 2 3 4 5 | Select top 1 name,surname, (year(takenDate)-year(birthDate)) from students join borrows on students.studentId = borrows.studentId where name = 'Alfie' and Surname='Watson' order by takenDate desc |
or
1 2 3 4 5 | Select top 1 name,surname, datediff(YY,birthDate,takenDate) from students join borrows on students.studentId = borrows.studentId where name = 'Alfie' and Surname='Watson' order by takenDate desc |
