CODEWITHSUNDEEP
google-bigquery
Timo Rietveld
Timo Rietveld

Reputation: 541

BigQuery: create array with dates of specified range

Does anyone know how to create an array in BigQuery containing dates of a specified range (where only the start and end date are needed to create the dates), like this:

[CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY), DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY), DATE_ADD(CURRENT_DATE(), INTERVAL 4 DAY), DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY)]

Upvotes: 0

Views: 375

Answers (1)

Mr.Batra
Mr.Batra

Reputation: 833

This should work.

select generate_date_array("2021-05-01", "2021-05-30") as date_array

Output JSON

[
  {
    "date_array": [
      "2021-05-01",
      "2021-05-02",
      "2021-05-03",
      "2021-05-04",
      "2021-05-05",
      "2021-05-06",
      "2021-05-07",
      "2021-05-08",
      "2021-05-09",
      "2021-05-10",
      "2021-05-11",
      "2021-05-12",
      "2021-05-13",
      "2021-05-14",
      "2021-05-15",
      "2021-05-16",
      "2021-05-17",
      "2021-05-18",
      "2021-05-19",
      "2021-05-20",
      "2021-05-21",
      "2021-05-22",
      "2021-05-23",
      "2021-05-24",
      "2021-05-25",
      "2021-05-26",
      "2021-05-27",
      "2021-05-28",
      "2021-05-29",
      "2021-05-30"
    ]
  }
]

Upvotes: 2

Related Questions