Bigquery Standard SQL Date to First Day of Month

Question

How do you convert a date in YYYY-MM-DD format to the first day of the month in BigQuery Standard SQL?

Answer 1

Use DATE_TRUNC:

#standardSQL
SELECT DATE_TRUNC('2017-02-17', MONTH);

Or, for a more interesting example where the input is strings in the format of YYYY-MM-DD:

#standardSQL
SELECT DATE_TRUNC(CAST(s AS DATE), MONTH)
FROM (
  SELECT '2017-02-17' AS s UNION ALL
  SELECT '2017-02-14' AS s
);