Calculate age based on multiple fields

Question

I have a table with a date of birth column, a date of death column and the date the dataset was extracted.

I want to calculate each person's age as either the date of their death or their age at the date of extract - not by the current date - with a new 'Age' column. I can't quite work it out at the moment.

Example data:

Current table

person_id	birth_datetimetime	death_datetime	extract_date
1234	1980-04-01T00:00:00	null	2021-12-31
8765	1925-05-04T00:00:00	2018-05-T00:00:00	2021-12-31
9102	1974-05-17T00:00:00	2021-01-31T00:00:00	2021-12-31
5678	2019-09-01T00:00:00	null	2021-12-31
3456	1947-04-01T00:00:00	2016-06-14T00:00:00	2021-12-31

Desired output

person_id	birth_datetimetime	death_datetime	extract_date	Age
1234	1980-04-01T00:00:00	null	2021-12-31	41
8765	1925-05-04T00:00:00	2018-05-T00:00:00	2021-12-31	93
9102	1974-05-17T00:00:00	2021-01-31T00:00:00	2021-12-31	47
5678	2019-09-01T00:00:00	null	2021-12-31	2
3456	1947-04-01T00:00:00	2016-06-14T00:00:00	2021-12-31	69

Desired output in second row date of birth 1925-05-04T00:00:00 and date of death is 2018-05-T00:00:00 it not understand can you explain. — Piyush Kachhadiya
– Piyush Kachhadiya, Commented Mar 2, 2022 at 10:33
How 'accurate' does the Age calculation need to be? If someone is born 2020-12-31 and dies on 2021-01-01, they're really 0 years and 1 day old, but some calculations would say the two dates are in different years and therefore yield a 1 year difference. — MatBailie
– MatBailie, Commented Mar 2, 2022 at 11:16

Mikhail Berlyant · Accepted Answer · 2022-03-02 10:36:12Z

3

Consider below approach

select *, date_diff(ifnull(death_datetime, extract_date), date(birth_datetimetime), year) as age from your_table

if applied to sample data in your question - output is

answered Mar 2, 2022 at 10:36

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MatBailie Over a year ago

Row 2's DoB is 4th May, but they died on 1st May, so they should be 92 with that data set.

Piyush Kachhadiya Over a year ago

Desired output in second row date of birth 1925-05-04T00:00:00 and date of death is 2018-05-T00:00:00 it not understand can you explain.

Mikhail Berlyant Over a year ago

i think 2018-05-T00:00:00 is a typo, so I just replaced it with 2018-05-01T00:00:00

Raven52 Over a year ago

Don't worry, I just made those up for illustration purposes.

Mikhail Berlyant Over a year ago

@MatBailie - agree with you, formally it should be 92. Similarly for third row - it should be 46. But looked to me that OP's "rounding" logic required 47, so I 've chosen not to overcomplicate stuff :o)

Collectives™ on Stack Overflow

Calculate age based on multiple fields

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related