Need to calculate and store age from birth to death date

Question

I have a sql table of people data named caduti with date column data_nasc(date of birth) and data_dec(date of death). I need to calculate age (in years) at death and set this value in eta column. I have tried some query, like this one:

UPDATE caduti
SET caduti.eta =     
    DATEDIFF(YY, caduti.data_nasc, caduti.data_dec) - 
    CEILING
     (0.5 * SIGN (
        (MONTH(caduti.data_nasc) - MONTH(caduti.data_dec)) * 50
         + DAY(caduti.data_nasc) - DAY(caduti.data_dec)
        )
      )

but it doesn't work. My need is not only t calculate the ages but also to store them in table column. Any help?

score 0 · Accepted Answer · answered Oct 06 '17 at 08:30

0

Try this

update 
caduti
set caduti.eta = (year(data_dec) - year(data_nasc) - (date_format(data_dec, '%m%d') < date_format(data_nasc, '%m%d')));

answered Oct 06 '17 at 08:30

Ketan Patil

1,222
13
21

Thank you for your reply. This solution is very simple and efficient and solve my problem. – Pier67 Oct 06 '17 at 09:05

Need to calculate and store age from birth to death date

1 Answers1