How to calculate age from date of birth MySQL query

No comments
Sometimes, you need to extract age from database of a person or it can be an employee or anyone, then we try to find a technique to fetch the date of birth details, although we can calculate the age via PHP or any language that a developer use to develop application writing some lines of code. However, if we can fetch all these information by writing a line or two in MySQL query then why should we write functions in programming language? We can write a SQL statement that will be much faster than fetching records from database then passing the value in a function and then the function will return the required result. So let's start with the SQL query:

Before moving ahead, I would like to tell you that I will be using using MySQL TIMESTAMPDIFF() function to fetch date of birth.

What is TIMESTAMPDIFF() function in MySQL?

The MySQL TIMESTAMPDIFF() function returns a result after subtracting a datetime expression from another datetime expression. In this function, you will need to pass two date or datetime values and an unit (unit can be in, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND or FRAC_SECOND (micro seconds)).

An Example of using TIMESTAMPDIFF() function 

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

Unit: Unit may be in YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND or FRAC_SECOND.

datetime_expr1: It can be any date for example 12/12/2012.
datetime_expr2: It can be any date for example 12/12/2012.


And now the time to calculate age from date of birth

 Assume, we have a MySQL table called employee and we have columns as emp_id, emp_name, emp_dob and we want fetch all the records of employees with the age in years then we will run below query.

SELECT emp_id, emp_name, TIMESTAMPDIFF(YEAR, emp_dob, CURDATE()) AS ageinyears FROM employee;

Also read What is maximum length for MySQL TEXT field type?

I hope, this tutorial will help you calculating age from date of birth by running above query. 

No comments :

Post a Comment