How to Calculate a Customer's Age from their Date of Birth in MySQL?

Getting Age from Date of Birth in MySQL

To calculate the age of a customer based on their date of birth, it is necessary to subtract the date of birth from the current date. However, the provided expression DATEDIFF(year, customer.dob, "2010-01-01"); is incorrect as it uses a fixed date rather than the current date.

The following formula accurately calculates the age in MySQL:

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

This formula subtracts the year of birth from the current year to calculate the age. It also checks if the customer's birthday has not yet passed in the current year by comparing the date formats '00-%m-%d' for both the current date and the date of birth. If the customer's birthday has not yet passed, the age is decremented by one.

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