Great! Answering your own questions on email lists can be therapeutic ;-)
Why not just use EXTRACT(year from age(dob)) --Andries > On May 25, 2016, at 7:39 AM, John Omernik <j...@omernik.com> wrote: > > 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 >>
