CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Sin
Sin

Reputation: 161

GCP BigQUery Calendar Table - Fiscal Year and Month

I am building a Calendar Table in Biquery. How can I get Fiscal month and Year from Calendar Date in GCP BigQuery? Fiscal Year - Jul to June Below is the SQL I have so far


SELECT
  date,
  FORMAT_DATE("%A", date) as Day_OF_WEEK,
  EXTRACT(DAY FROM date) as the_day,
  EXTRACT(MONTH FROM date) as the_month,
  EXTRACT(YEAR FROM date) AS year,
  FORMAT_DATE("%b", date) as Month_Name,
  FORMAT_DATE("%B", date) as Month_Name_Full,
  EXTRACT(WEEK FROM date) AS week,
 FORMAT_DATE("%d-%m-%E4Y", date) as Date_DDMMYYYY,
 FORMAT_DATE("%Y%m", date) as Date_YYYYMM,
 FORMAT_DATE("%Y-%m", date) as Date_YYYY_MM,
 FORMAT_DATE("%m-%Y", date) as Date_MM_YYYY,

How do I get this?
 --FORMAT_DATE("%Y%m", date) as FISCALMonth,
 --FORMAT_DATE("%Y%m", date) as FISCALYEAR,
 
 FROM UNNEST(GENERATE_DATE_ARRAY('2010-01-01', '2050-12-31')) AS Date
ORDER BY date;

Output

Upvotes: 1

Views: 6203

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

SELECT
  date,
  extract(month from date) as calendar_month,
  extract(year from date) as calendar_year,
  extract(month from date_add(date, interval 6 month)) as fiscal_month,
  extract(year from date_add(date, interval 6 month)) as fiscal_year,
from unnest(generate_date_array('2020-01-01', '2021-12-31', interval 1 month)) as date
order by date;

output

enter image description here

Upvotes: 1

Related Questions