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
