What is the Bigquery Date syntax to call current date and yesterday Date in yyyymmdd format dynamically

user_9764821 0
Viewed 295

How to call Current Date and Yesterday Date in yyyymmdd format dynamically without mentioning explicitly in the query as mentioned below

WHERE _TABLE_SUFFIX BETWEEN [YESTERDAYDATE] AND [TODATE] in in yyyymmdd format

WHERE _TABLE_SUFFIX BETWEEN '20220222' AND '20220223' in yyyymmdd format

In the below query, how to call [YESTERDAYDATE] AND [TODATE]in yyyymmdd format dynamically without mentioning explicitly in the query '20220222' AND '20220223'

select 
stream_id,app_info.id,app_info.version,  
event_date,event_name, 
  param1.value.string_value as category,
  param2.value.string_value as action,
  param3.value.string_value as label,
  count(1) as totalevent,count( distinct user_pseudo_id ) as uniqueusers
From `abc_150838411.events_intraday_*`,
UNNEST(event_params) as param1,
UNNEST(event_params) as param2,
UNNEST(event_params) as param3
WHERE _TABLE_SUFFIX BETWEEN '20220222'  AND '20220223'
and param1.key='category' 
and param2.key='action' 
and param3.key='label' 
group by 1, 2, 3, 4, 5,6,7,8
order by totalevent desc

Pls. Help Thanks

google-bigquery
2 Answers

You can use FORMAT_DATE function as follows,

SELECT FORMAT_DATE("%d%m%y", current_date())
user_86596430

use below

WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', current_date - 1)  AND FORMAT_DATE('%Y%m%d', current_date)
user_52219440
Related