INTERVAL handling in BigQuery's GENERATE_DATE

I'm using GENERATE_DATE_ARRAY to obtain all end-of-month (EOM) dates between two dates. According to the

It seems that after February, the process get screwed and keeps 28 as the end of month until the final date.

Is there something I'm missing? Or maybe this is a bug?

CodePudding user response：

As pointed out by @Jaytiger comment, this may be something expected, although not clearly documented.

As a workaround, this approach may be used:

SELECT DATE_SUB(day, INTERVAL 1 DAY) FROM
  UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2022-04-01', INTERVAL 1 MONTH)) day

In other words, instead of generating the EOM dates, it is best to generate the start-of-month dates and the subtract a day.

CodePudding user response：

Consider also below approach (note use of LAST_DAY function)

select last_day(day, month) from
unnest(generate_date_array('2021-01-01', '2022-04-01', interval 1 month)) day