CODEWITHSUNDEEP
sqlgoogle-bigquery
Pentium10
Pentium10

Reputation: 208042

How to generate with scripting INTERVAL 1 <day|week|month>?

We are trying to find a syntax to generate the DAY|WEEK|MONTH options from the 3rd param of date functions. 

DECLARE var_date_option STRING DEFAULT 'DAY';
select GENERATE_DATE_ARRAY('2019-01-01','2020-01-01',INTERVAL 1 WEEK)
                                            dynamic param here -^^^

Do you know what's the proper syntax to use in DECLARE and that should be converted to valid SQL.

Upvotes: 2

Views: 120

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Below is for BigQuery Standard SQL

Those DAY|WEEK|MONTH are LITERALs and cannot be parametrized
And, as you know - dynamic SQL is also not available yet

So, unfortunately below is the only solution I can think of as of today 

#standardSQL
DECLARE var_date_option STRING DEFAULT 'DAY';
DECLARE start_date, end_date DATE;
DECLARE date_array ARRAY<DATE>;

SET (start_date, end_date, var_date_option) = ('2019-01-01','2020-01-01', 'MONTH');

SET date_array = (
  SELECT CASE var_date_option 
    WHEN 'DAY' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)
    WHEN 'WEEK' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 WEEK)
    WHEN 'MONTH' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 MONTH)
  END
);

SELECT * FROM UNNEST(date_array) AS date_dt;

Upvotes: 3

Related Questions