Getting data from current week in BigQuery-CodePudding

i want to get the data from the current week, i tried this:

SELECT *
FROM `grouponi_groupon.tb_orders_items`
Where cast(creation_ts as date) between DATE_TRUNC(cast(creation_ts as date), WEEK) and current_date()
and item_id = 1067222

but I still get wrong dates. Also I want that the first day of the week will be Sunday and not Monday. Whats wrong with my query and how do I change the 1st day of the week to Sunday?

CodePudding user response：

Your query should work if you replace DATE_TRUNC(cast(creation_ts as date), WEEK) by DATE_TRUNC(current_date(), WEEK). Also, when using the keyword WEEK in bigquery you have a week starting on Sunday, as said in Google Doc