BigQuery Load Error : Could not parse 'MM/DD/YYYY' as date for field shipped from CSV file

While loading CSV file with date values, bq load fails:

id,shipped,name
0,1/10/2019,ryan
2,10/1/2013,henry

Error while reading data, error message: Could not parse '1/10/2019' as date for field shipped (position 1) starting at location 17

Solution:

While doing bq load only YYYY-MM-DD is supported:

When you load CSV or JSON data, values in DATE columns must use 
the dash (-) separator and the date must be in the following 
format: YYYY-MM-DD (year-month-day).

You can,

• Load the CSV file as-is to BigQuery (i.e. convert the column to STRING datatype)
• Create a BigQuery view that transforms the shipped field from a string to a recognized date format. Use SELECT id, PARSE_DATE('%m/%d/%Y', shipped) AS shipped, name
• Use that view for your analysis

Ryan-Dallas

posted on 01 Feb 21