Try and search as I might, I still cannot figure this out and although https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_add has assisted a bit, I am still stuck. I am trying to group dates into weeks but keep getting either one of the two errors below the code.
day bitcoin_total dash_total
2009-01-03 1 0
2009-01-09 14 0
2009-01-10 61 0
The desirable outcome would be the date at the start of the week (could be Monday or Sunday, whichever)
day bitcoin_total dash_total
2008-12-28 1 0
2009-01-04 75 0
This seems to be a common question but most of the answers are for T-SQL and not standard SQL. My date column is type Date
but that is the return type so that should not be a problem.
DATE_ADD(week, DATE_DIFF(week, 0, day), 0) Date
FROM
my_table
GROUP BY
DATE_ADD(week, DATE_DIFF(week, 0, day), 0)
ORDER BY
DATE_ADD(week, DATE_DIFF(week, 0, day), 0)
I am getting a Unrecognized name: week at [2:10]
error with the above code or Error: Expected INTERVAL expression at [2:29]
if I change the date_expression
in the DATE_ADD function to, say, DATE "2009-01-01"