Using The Built-In Date Range Filter To Dynamically Update BigQuery Results
BigQuery, unlike other flavors of SQL, breaks out its tables by day. Each table has a suffix with the date included, but what if you want to allow your end-user to dynamically query only tables from certain dates?
By using table wildcard operators in conjunction with the built-in Date Range filter, this is definitely possible!
For StandardSQL
select
*
from
`table_prefix_*`
where
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', [daterange_start]) AND FORMAT_DATE('%Y%m%d', [daterange_end])For LegacySQL
#legacySQL
SELECT
*
FROM
TABLE_DATE_RANGE([project:dataset.tableprefix.table_prefix], TIMESTAMP([daterange_start]), TIMESTAMP([daterange_end]))Updated 03-02-2023
