Well I need to include the Staples "That was Easy" button here... I tried:
EXTRACT(year from cast(age(dob) as INTERVAL YEAR)) as yr_age And it worked! Self Answering question is self answering... On Wed, May 25, 2016 at 9:35 AM, John Omernik <j...@omernik.com> wrote: > Hey all, simple question, I have a field, dob, I want to get the current > age from... > > I have: > > cast(age(dob) as INTERVAL YEAR) as yr_age > > Which works pretty well, as you can see below, however, I'd like a column > that is just the integer age, no months, no P/Y etc. Now, I can play with > string manipulation for a really ugly query, but I was hoping there may be > a way to just convert the INTERVAL YEAR (or heck even the return of the age > function) to the number of years... > > So instead of P25Y, it would be 25, P52Y1M would be 52, P21Y8M would be 21 > etc. > > > Any easy way to do this? If I have to go the string route, is there an > easy way to do it in drill as well (I miss the Hive "regexp_extract" in > this case) > > > John > > > select dob, age(dob) as cur_age, cast(age(dob) as INTERVAL YEAR) as > yr_age from am_joined where substr(dob, 6, 2) <> '00' limit 10; > > > +-------------+------------------+----------+ > > | dob | cur_age | yr_age | > > +-------------+------------------+----------+ > > | 1991-09-29 | P300M5DT18000S | P25Y | > > | 1965-01-06 | P625M17DT18000S | P52Y1M | > > | 1995-01-12 | P260M4DT18000S | P21Y8M | > > | 1988-08-01 | P338M19DT18000S | P28Y2M | > > | 1984-03-05 | P392M9DT18000S | P32Y8M | > > | 1980-12-13 | P431M17DT18000S | P35Y11M | > > | 1976-11-28 | P480M23DT18000S | P40Y | > > | 1984-11-12 | P383M27DT18000S | P31Y11M | > > | 1965-01-20 | P625M3DT18000S | P52Y1M | > > | 1984-04-19 | P390M24DT18000S | P32Y6M >
