Search code examples
sqlgoogle-bigquery

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

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
Solution

  • 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.