BigQuery - DATE_TRUNC on string column

I have a table with date columns stored as string:

amount  date_create
100     2018-01-05
200     2018-02-03
300     2018-01-22

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

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

with result as

Row month       amount_m     
1   2018-01-01  400  
2   2018-02-01  200  

Mike-Barn

posted on 07 Jun 19