Getting a Monthly Date Range

How can you make a date range in a big query? A date range starts from 29th of the month and ends with 28th of the next month. It should be like this

Date | Starting Date | Ending Date

03-13-2020 | 02-29-2020 | 03-28-2021

06-30-2020 | 06-29-2020 | 07-28-2021

01-01-2021 | 12-29-2020 | 01-28-2021

11-11-2021 | 10-28-2021 | 11-29-2021

Actually, i make an article on it. Check this out:

https://www.theaccountingtactics.com/2021/12/BigQueryBQ-DateProblems-DateSituations-that-are-Hard-to-Analyze-and-Takes-Time-ToCrack%20.html?m=1

Answer

Consider below approach

create temp function set_day(date date, day int64) as (
  ifnull(
    safe.date(extract(year from date), extract(month from date), day), 
    last_day(date)
  )
);
select Date, 
  set_day(Starting_Date, 29) as Starting_Date,
  set_day(Ending_Date, 28) as Ending_Date
from (
  select *, if(extract(day from Date) < 29, 
      struct(date_sub(Date, interval 1 month) as Starting_Date, Date as Ending_Date),
      struct(Date as Starting_Date, date_add(Date, interval 1 month) as Ending_Date)
    ).*
  from your_table 
)
     

if applied to sample data as in your question

with your_table as (
  select date '2020-03-13' Date union all
  select '2021-03-13' union all
  select '2020-06-30' union all
  select '2021-01-01' union all
  select '2021-11-11' 
)            

output is

You can test whole stuff using below

create temp function set_day(date date, day int64) as (
  ifnull(
    safe.date(extract(year from date), extract(month from date), day), 
    last_day(date)
  )
);
with your_table as (
  select date '2020-03-13' Date union all
  select '2021-03-13' union all
  select '2020-06-30' union all
  select '2021-01-01' union all
  select '2021-11-11' 
) 
select Date, 
  set_day(Starting_Date, 29) as Starting_Date,
  set_day(Ending_Date, 28) as Ending_Date
from (
  select *, if(extract(day from Date) < 29, 
      struct(date_sub(Date, interval 1 month) as Starting_Date, Date as Ending_Date),
      struct(Date as Starting_Date, date_add(Date, interval 1 month) as Ending_Date)
    ).*
  from your_table 
)