Certain Range of Date in each Month-CodePudding

I'd like to have a range of day 20th - 25th in each month in BigQuery but i dont know what syntax should i use. For ex: Jan 20 - 25 Feb 20 - 25 and so on

I only can think of creating a CTE for every month then union all those.

CodePudding user response：

Consider below query.

SELECT DATE_ADD(month, INTERVAL day - 1 DAY) date_range,
  FROM UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-03-01', INTERVAL 1 MONTH)) month,
       UNNEST(GENERATE_ARRAY(20, 25)) day;

Query results

Below seem to be more simple than my original answer and you could adjust date range by specifying condition on WHERE clause.

SELECT *
  FROM UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-12-31', INTERVAL 1 DAY)) date_range
 WHERE EXTRACT(DAY FROM date_range) BETWEEN 21 AND 25

For the usecase that you commented,

WHERE EXTRACT(DAY FROM date_range) >= 21 OR EXTRACT(DAY FROM date_range) = 1