Big Query Legacy Vs Standard Date Ranges Query

Bigquery supports both Standard and Legacy SQL, below are the few tips how we can migrate SQLs from legacy to standard SQL.

Legacy

For date shard table:

SELECT * FROM

TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

For partitioned table use

_PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_TIMESTAMP(), -4, 'Day'))

AND TIMESTAMP(CURRENT_TIMESTAMP())

Standard

For date shard table:

SELECT * FROM

FROM `PROJECT.DATASET.TABLE_*`

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

For partitioned table replace _TABLE_SUFFIX with DATE(_PARTITIONTIME)