Yep, that works without the extra CAST, I didn't see the Y in the original age return, I assumed it had to be as year before I could use it, I assumed wrong. I guess that's my superpower, being wrong and answering my own questions on lists so others can learn from my mistakes ;)
On Wed, May 25, 2016 at 10:51 AM, Andries Engelbrecht < aengelbre...@maprtech.com> wrote: > 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 > >> > >
