Sql Query To Find Age From Date Of Birth In Sql – Query Examples

Sql Query To Find Age From Date Of Birth In Sql

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

Method-2

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-1 and Method-2 Results

Method-3

Method-3 Results

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 data

Example-2: List all students name, surname and age sorted by their age

or

Result: 505 rows listed

Example-3: List the name and surname of students whose age 17

or

Result: 136 rows listed

Example-4: How old was the student named Alfie Watson when he has got the last book

or

Result: 1 row(s) listed.

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.

YOU MAY WANT TO SEE OUR ALL EXAMPLES PAGE, THEN CLICK HERE

About the author

admin

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: