richardsalt

--Generate Date Dimension Table for BigQuery - adjust date range as required
--this is a modified version of ewhauser/date_dim.sql gist
SELECT
  d as id,
  FORMAT_DATE('%Y-%m', d) as yearMonth,
  FORMAT_DATE('%F', d) as yearMonthDay,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%m', d) as monthPadded,
  EXTRACT(DAY FROM d) as day,  

richardsalt

SELECT
  FORMAT_DATE('%F', d) as id,
  d AS full_date,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(WEEK FROM d) AS year_week,
  EXTRACT(DAY FROM d) AS year_day,
  EXTRACT(YEAR FROM d) AS fiscal_year,
  FORMAT_DATE('%Q', d) as fiscal_qtr,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%B', d) as month_name,