Calculate age based on date of birth

Introduction

Calculating a person's age from their date of birth is pretty simple. We just have to find the difference between the date of birth and the current date in years, and as a result, we will get the age of the person.

In SQL, to find the age from the date of birth, we will need to use these functions:

• NOW(): It returns the current date and time.
• DATEDIFF(): It finds the difference between two dates passed to it.
• FROM_DAYS(): This function finds the date from the specified numeric date value. It works according to the Gregorian Calendar.
• DATE_FORMAT(): This function formats a specified date as per the format specifier (%e for date, %c for month, %Y for year, and many more).

Let's see how to use these functions to convert the date of birth of a person into their age in the next section.

How do you Convert Date of Birth to Age in SQL?

We can find the age of a person from their date of birth by using this formula:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),'DATE_OF_BIRTH')), '%Y') + 0 AS age;

In the above formula, we are first finding the difference between the current date (NOW()) and the date of birth (in YYYY-MM-DD) using the DATEDIFF() function.

The difference is then passed to the FROM_DAYS() function that calculates the time span of the date difference in the YYYY-MM-DD format.

The DATE_FORMAT() function is used to get the year from the YYYY-MM-DD using the format specifier '%Y', returning the age of the person as a 4-digit number (for e.g., 00320032 or 00290029).

To get the age in a simple INT form without the unnecessary 0s, we add the 4-digit number with 00, resulting in the correct age format.

Examples of Calculating Age from Date of Birth in SQL

Let us have a look at some examples to have a clear understanding of this concept.

Demo Database

Employee Table

This table has 4 columns: emp_idemp\_id, first_namefirst\_name, last_namelast\_name, and date_of_birthdate\_of\_birth. The PRIMARY KEY is emp_idemp\_id of every employee. There are 5 employees in this table.

Every example will make an update to this table and not an already updated table.

Displaying the age of each employee in the table

We will display each employee's age using their date of birth from the Employee table.

Code:

SELECT *, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), date_of_birth)), '%Y') + 0 AS age FROM employee1;

Output:

In the above example, we are displaying the details of each employee with their age using their date of birth.

Updating the table to add a new column for age

We will now update the table to add a new column to store each employee's age, instead of just displaying them.

Code:

ALTER TABLE employee ADD age INT; UPDATE employee SET age = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), date_of_birth)), '%Y') + 0; SELECT * FROM employee;

Output:

In the above example, we are adding a new column in the Employee table, named age, which contains each employee's age calculated using their date of birth.

Finding the age of someone using their date of birth

We will now find the age of a person, whose date of birth is stored in a variable.

Code:

SET @dob_of_ayush = '2000-08-29'; SET @age_of_ayush = DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), dob_of_ayush)), '%Y') + 0; SELECT @age_of_ayush;

Output:

In the above example, we have calculated the age of a person using a variable that stores their date of birth.

Learn More About Time and Date Format in SQL

This is the complicated side of SQL, when dealing with date and time, we have to make sure that the format of the date-time we are trying to insert in a table matches the format of the date-time column of the database.

Following are the type of date-time formats in SQL:

• DATE: YYYY-MM-DD
• DATETIME: YYYY-MM-DD HH:MI
• TIMESTAMP: YYYY-MM-DD HH:MI
• YEAR: YYYY or YY

To learn more about the date-time format in SQL, click here.

Conclusion

• We can calculate the age of a person using their date of birth using these functions altogether: NOW(), DATEDIFF(), FROM_DAYS(), and DATE_FORMAT().
• The formula to convert the date of birth into age is DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),'DATE_OF_BIRTH')), '%Y').
• We can display the age of people using their date of birth. We can also update the table to store the age of each person.