How can I set up a BigQuery query with a date range includes the current date

Question

I'm trying to query a database of hits in BigQuery starting at a specified go live date and I want the query to continuously run each day afterwards. Is there a way to prevent hard coding a date range into a query but have the current date instead?

What I've got so far is

WHERE SUBSTR(_TABLE_SUFFIX, 1, 8) BETWEEN '20200701' AND '20211231'

Its the second date that I want to change to be some sort of variable for the current date?

Thanks

Answer 1

Use CURRENT_DATE() with FORMAT_DATE():

SELECT FORMAT_DATE("%Y%m%d", CURRENT_DATE()) AS formatted;