How to calculate age based on DATE of birth in Mysql based on month and day?

Question

I have the appointment below, but she ignores the month and day of birth

SELECT
    FLOOR(DATEDIFF(NOW(), c.nascimento) / 365) AS idade
FROM
    clientes c

In the result, most come right, but sometimes have difference of one year. How to make the consultation, but taking into account the month and day of birth?

Answer 1

There are several possibilities. Yours works, but as not every year has 365 days, it will never be needed.

A possibility:

SELECT
    YEAR(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(c.nascimento))) AS idade
FROM
    clientes

It could be so too:

TIMESTAMPDIFF(YEAR, c.nascimento, NOW()) AS idade

Or is she really ugly:

SUBSTRING(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(c.nascimento)), 3, 2) AS idade

Success!

Answer 2

The best and most refined alternative is this:

SELECT 
   TIMESTAMPDIFF(YEAR, c.nascimento, CURDATE()) as idade 
FROM
   clientes c

This function is great.

Answer 3

This function gets the exact number of full years based on the birthday 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 whether the anniversary of today’s date (or reference date) has not yet arrived.

Kept with variable instead of current date to make it easy to calculate age from other references.

If you prefer to replace dHoje, use CURRENT_DATE or CURDATE() instead of NOW()

Answer 4

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

Second 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

Answer 5

There’s that shape too:

DATE_FORMAT(coluna_nascimento, '%d/%m/%Y') AS Nascimento, TRUNCATE(DATEDIFF(NOW(), coluna_nascimento)/365.25, 0) AS Age

365.25 is to compensate for the leap year