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 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
Method -1
|
1 |
Select name,surname, birthdate, getdate() as CurrentDate, year(getdate())-year(birthDate) as age from students |
Method-2
|
1 2 |
Select name,surname,birthdate,getdate() as CurrentDate, datediff(YY,birthDate,getdate()) as age from students |
The method we did above with the datediff method is the simplest method of calculating age, but it does not always give accurate results. We can produce more accurate results using the following method.
Method-3
|
1 2 3 4 |
Select name,surname,birthdate,getdate() as CurrentDate, datediff(MONTH,birthDate, getdate())/12 - case when month(birthDate)=month(getdate()) and day(birthdate) > day(getdate()) then 1 else 0 end as age from students |
As you see from results some records are same and some are different
Explain Of The 3. Method
First we find the month difference between the two dates. We calculate the age by dividing this number by 12. With the “case when month(birthDate)=month(getdate()) and day(birthdate) > day(getdate()) then 1 else 0” code, we check whether the month of the current date and the month of the birth date are the same month and Is the day of the month she was born greater than the day of the current date?
I will explain with a table with some data below.
Explanation of some expressions in the table:
Method-1 => year(getdate()) – year(birthdate)
Method-2 => datediff(Year,birthdate,getdate())
Method-3 => datediff(MONTH,birthDate, getdate())/12 –
case when month(birthDate)=month(getdate()) and day(birthdate) > day(getdate()) then 1 else 0 end
Method-4 => datediff(MONTH,birthDate, getdate())/12
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 |
In this post we have learned how to calculate age from date of birth in sql.
If you want to examine “calculating age from date of birth” or other sql query examples click the following link.
