How to Calculate Age from Date of Birth in MySQL?

Retrieving Age from Date of Birth in MySQL

To calculate the age of a customer based on their date of birth stored in a MySQL field, it is essential to consider the concept of age calculation. Age is determined by the difference between the current date and the date of birth, with the caveat that if the date of birth is within the current year, the age should be decremented by one.

Solution:

To achieve this, the following query can be employed:

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - 
(DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

Breakdown:

• DATE_FORMAT(NOW(), '%Y'): Retrieves the current year.
• DATE_FORMAT(dob, '%Y'): Retrieves the year component from the dob field.
• DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d'): Checks if the current year has already passed the customer's birthday, resulting in a Boolean value (1 for true, 0 for false).
• Subtracting this Boolean value from the year difference adjusts the age to accurately reflect the current age of the customer.

Example:

Suppose the dob field contains the following value: 1985-03-15. For the current date of 2023-04-28, the query would produce the following result:

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - 
(DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

SELECT 2023 - 1985 - (20230428 < 19850315) AS age

SELECT 38 - 0 AS age

age = 38

Therefore, using this enhanced query, you can retrieve the age of a customer based on their date of birth stored in a MySQL field with greater accuracy.

The above is the detailed content of How to Calculate Age from Date of Birth in MySQL?. For more information, please follow other related articles on the PHP Chinese website!