BigQuery - DATE_TRUNC on string column

I have a table with date columns stored as string:

amount  date_create\n100     2018-01-05\n200     2018-02-03\n300     2018-01-22\n

In order to query the table, BigQuery Standard SQL - use PARSE_DATE function

#standardSQL\nWITH `project.dataset.table` AS (\n  SELECT 100 amount, '2018-01-05' date_create UNION ALL\n  SELECT 200, '2018-02-03' UNION ALL\n  SELECT 300, '2018-01-22' \n)\nSELECT \n  DATE_TRUNC(PARSE_DATE('%Y-%m-%d', date_create), MONTH) AS month, \n  SUM(amount) AS amount_m \nFROM `project.dataset.table`  \nGROUP BY 1  \n

with result as

Row month       amount_m     \n1   2018-01-01  400  \n2   2018-02-01  200  

Mike-Barn

posted on