Calculate Age from Date of Birth in SQL

In this article we are going to discuss on calculating the age of a person when his/her Date of Birth information is available with us. 

We request you to go through with our article on “Date Operations in SQL”. This article will form a base for your Data related knowledge in SQL queries.

 

Now, let us discuss on the topic:

If we want to calculate Age of a person from his/her data of birth, then we need to subtract the date of birth from today’s date and divide the result by 365 to get Age in years.

We would need a JulianDay() function to solve this problem.

 

Short story on JulianDay() function:

As per Wikipedia, “The Julian day is the continuous count of days since the beginning of the Julian period, and is used primarily by astronomers, and in software for easily calculating elapsed days between two events (e.g. food production date and sell by date). The Julian day number (JDN) is the integer assigned to a whole solar day in the Julian day count starting from noon Universal Time, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar)”

 

Basically we use the Julian Day function to use a very old date as a point of reference.

 

Let us take one example and see how to calculate Age from DOB

Date of Birth given = “1968-12-08”

Julian Day for the Date of Birth = 2440198

 

Today’s Date = “2022-02-20”

Julian Day for Today’s Date = 2459630

 

Age = ( (Julian Day for Today’s Date) - (Julian Day for the Date of Birth) ) / 365

        = 53.23 years

 

Let us write query for the same in SQL:

SELECT * FROM Customers

 

Query:

SELECT Name, DOB, round((JulianDay('now') - JulianDay(DOB))/365, 2) as Age_in_Years

FROM Customers;

Output:

Simple yet effective way of finding age.

 

Would you like to read our article on hot topic Difference between Delete, Truncate and Drop commands in SQL?