Generate date table BigQuery

 select

    day as date_bk,

    day as date,

    cast(day as datetime) as date_time,

    cast(day as timestamp) as date_timestamp,

    format_date('%Y-%m-%d',day) as date_string,

    extract(year from day) AS year,

    extract(quarter from day) AS quarter,

    'Q' || extract(quarter from day) AS quarter_string,

    extract(month from day) AS month,

    format_date('%B', day) AS month_string,

    format_date('%b', day) AS month_abbr_string,

    extract(week from day) AS week_sun,

    cast(format_date('%W', day) as integer) AS week_mon,

    extract(isoweek from day) AS iso_week,

    'W' || format('%02d',extract(isoweek from day)) AS iso_week_string,

   

    extract(day from day) AS day_of_month,

    extract(dayofyear from day) AS day_of_year,

    extract(dayofweek from day) AS day_of_week_sun,

    format_date('%w', day) AS day_of_week_mon,

    format_date('%A', day) AS day_of_week_string,

    format_date('%a', day) AS day_of_week_abbr_string,

    cast(format_date('%Y%m',day) as integer) as year_month,

    format_date('%Y %B',day) as year_month_string,

    format_date('%Y %b',day) as year_month_abbr_string,

    cast(format_date('%Y%m%d',day) as integer) as year_month_day,

    extract(year from day) * 10000 + extract(month from day)*100 + extract(isoweek from day) AS year_month_iso_week,    

    extract(year from day) * 100 + extract(isoweek from day) AS year_iso_week,    

    extract(year from day) * 100 + extract(quarter from day) AS year_quarter,    

    extract(year from day) || 'Q' || extract(quarter from day) AS year_quarter_string,    

    case when extract(dayofweek from day) between 2 and 6 then true else false end as is_weekday,

    last_day(day,year) = day as is_last_date_of_year,

    last_day(day,month) = day as is_last_date_of_month,

    last_day(day,isoweek) = day as is_last_date_of_isoweek,

    last_day(day,year) as last_date_of_year,

    last_day(day,month) as last_date_of_month,

    last_day(day,isoweek) as last_date_of_iso_week

from unnest(

    generate_date_array('2001-01-01', '2029-12-31', interval 1 day)

) AS day