I have the query below, but it ignores the month and day of birth
SELECT
FLOOR(DATEDIFF(NOW(), c.nascimento) / 365) AS idade
FROM
clientes c
In the result, most comes right, but sometimes has a difference of one year. How to make the query, but taking into account the month and day of birth?
There are several possibilities. Its works, but as not every year has 365 days, it will never be accurate.
One possibility:
SELECT
YEAR(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(c.nascimento))) AS idade
FROM
clientes
It could be this way too:
TIMESTAMPDIFF(YEAR, c.nascimento, NOW()) AS idade
Or it's very ugly:
SUBSTRING(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(c.nascimento)), 3, 2) AS idade
Success!
This function gets the exact number of full years based on the anniversary date:
SELECT YEAR(dHoje)-YEAR(dNasc)-IIF(MONTH(dHoje)*32+DAY(dHoje)<MONTH(dNasc)*32+DAY(dNasc),1,0)
Note that it is not based on approximations and calculations, but rather considers the current day to determine if the birthday of today's date (or reference) has not yet arrived.
I kept with the variable instead of the current date to make it easy to calculate the age from other references.
If you prefer to override dHoje, use CURRENT_DATE or CURDATE () instead of NOW ()
The best and most refined alternative is this:
SELECT
TIMESTAMPDIFF(YEAR, c.nascimento, CURDATE()) as idade
FROM
clientes c
This function is great.
1st Form
SELECT
YEAR(CURRENT_DATE) - YEAR(c.nascimento) - (DATE_FORMAT(CURRENT_DATE, '%m%d') < DATE_FORMAT(c.nascimento, '%m%d')) as idade
FROM
clientes c
2nd Form
SELECT
TIMESTAMPDIFF(YEAR, c.nascimento, CURRENT_DATE) as idade
FROM
clientes c
3rd Form
SELECT
YEAR(FROM_DAYS(DATEDIFF(CURRENT_DATE,c.nascimento))) as idade
FROM
clientes c
It also has this form:
DATE_FORMAT (child_file, '% d /% m /% Y') AS Birth, TRUNCATE (DATEDIFF (NOW (), child_file) /365.25, 0)
365.25 is to compensate for leap year