Google BigQuery :How to Generate Date Array by End of Month date

user_15215220 0
Viewed 2289

I am trying to generate dates by End of Month

SELECT
Date_Ranges
FROM 
    UNNEST(GENERATE_DATE_ARRAY('2020-01-31', DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH),
 INTERVAL 1 DAY), INTERVAL 1 Month)) AS Date_Ranges

But the Result I get is:

Date_Ranges
31/01/2020
29/02/2020
29/03/2020
29/04/2020
29/05/2020
29/06/2020
29/07/2020
29/08/2020
google-bigquery sql
2 Answers

Another option:

select last_day(dt, month) Date_Ranges
from unnest(generate_date_array('2020-01-01', current_date(), interval 1 month)) dt
user_52219440

It'll probably be easier to break your logic up. Something like this seems to work:

select date_sub(BOMs, interval 1 day) as EOM
from unnest(generate_date_array('2020-01-01', current_date(), interval 1 month)) BOMs

You can change the first 2 arguments in the generate_date_array function to get the specific window you want.

user_116438580
Related