Age from year and date column Netezza sql

I have data elements like ID; start_date; Birth_year. I want to calculate Age based on the birth_year column and start_date as shown in the AGE column.

I could only think of padding the birth year with ‘-01-01’ and then using that column to calculate Age.

Is there another efficient way to get Age from the birth year and start date in Netezza SQL?

Answer

Use the extract SQL function to get the year from start_date and do the age calcuation

E.g

select
    start_date,
    birth_year,
    -- assuming birth_year is an integer
    extract(year from start_date) - birth_year as age
from ... 

will give

 START_DATE | BIRTH_YEAR | AGE
------------+------------+-----
 2000-01-15 |       1985 |  15
 2010-06-20 |       1990 |  20
 2005-12-15 |       1992 |  13
 2007-08-17 |       1998 |   9
 2020-09-15 |       1999 |  21
(5 rows)

---

An alternative method to get more detailed age would be to use the Netezza ‘age’ SQL function –

select
    start_date,
    birth_year,
    age(start_date, to_date(birth_year, 'YYYY'))
from...

which will result in

 START_DATE | BIRTH_YEAR |           AGE
------------+------------+--------------------------
 2000-01-15 |       1985 | 15 years 14 days
 2010-06-20 |       1990 | 20 years 5 mons 19 days
 2005-12-15 |       1992 | 13 years 11 mons 14 days
 2007-08-17 |       1998 | 9 years 7 mons 16 days
 2020-09-15 |       1999 | 21 years 8 mons 14 days
(5 rows)