Bigquery: Getting first day of the week/month in standard SQL

Question

In Bigquery's legacy SQL, I can get the start of week for a date by using

SELECT DATE((UTC_USEC_TO_WEEK(TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-13 20:58:06 UTC')), 0)))

which returns 2017-04-09.

Is there a way to do this in BigQuery's standard SQL? There doesn't seem to be any equivalents for UTC_USEC_TO_WEEK and UTC_USEC_TO_MONTH.

Answer 1

This is better option that works now:

select DATE_TRUNC(date( '2008-12-25 15:30:00'), month)

Answer 2

It looks like BigQuery has a function named TIMESTAMP_TRUNC which may do what you want. It is referenced as the replacement for UTC_USEC_TO_DAY(t) in LegacySQL when used with a Day datepart. It also accepts Week and Month as a parameter which may meet your requirements.

TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', WEEK, 'UTC')

Here is the page for migrating from Legacy to Standard sql