Calculation of age in Bigquery

Question

Can anyone help me to find the alternative of age function in Bigquery. Below is some example of AGE function in netezza database. ex:

SELECT AGE(Current_date,'1994-07-16');

o/p: 27 years 2 mons 21 days

Thanks in advance.

Answer 1

Try this custom function. This function does not take care of leap years.

create function mydataset.age(_date date)
returns string  
as
(
    (
        select  concat(
            case when years = 0 then '' else  concat(cast(years as string),' Year ') end,
            case when months = 0 then '' else  concat(cast(months as string),' Month') end,
            case when days = 0 then '' else  cast(days as string) end,' Day'
            )
    from (
        select
        div(date_diff(current_date(),_date,day),365) years,
        div(mod(date_diff(current_date(),_date,day),365),30) months,
        mod(mod(date_diff(current_date(),_date,day),365),30) days
    )
    )
        
)

Answer 2

Consider below approach

create temp function age (dob date, base date) as ((
  select format('%i years %i months %i days', years, months, date_diff(base, temp_date2, day))
  from (
    select *,
      date_diff(base, temp_date, month) - day_check months,
      date_add(temp_date, interval date_diff(base, temp_date, month) - day_check month) temp_date2   
    from (
      select *,
        date_diff(base, dob, year) - month_check as years,
        date_add(dob, interval date_diff(base, dob, year) - month_check year) temp_date
      from (
        select 
          if(extract(month from base) < extract(month from dob), 1, 0) month_check,
          if(extract(day from base) < extract(day from dob), 1, 0) day_check
      )
    )
  )    
));
select age('1994-07-16', current_date) as age    

with output