You may run into issues with your date column during uploads:
- Uploads failing because of incorrect formatting of your date column – this applies mainly to Manual option under Data Types.
- TIMESTAMP (2020-03-05 11:00:00 UTC) data type being uploaded instead of DATE (2020-03-05) – this applies mainly to Autodetect option under Data Types.
If you are about to upload a table via Manual or Autodetect option with a date column, it’s important to make sure the date column is formatted as YYYY-MM-DD.
Real life example for Manual option:
BigQuery is quite sensitive to data types and it only accepts DATE data type in YYYY-MM-DD format. So your date needs to look like 2020-05-30 (=May 30th, 2020) and not like 5/30/2020 for example.
Here is how to achieve YYYY-MM-DD formatting in Google Sheets:
Once you set your date column to YYYY-MM-DD and upload your spreadsheet to BigQuery, your column will really look like a date in BigQuery schema:
And then as YYYY-MM-DD date in Preview:
The same formatting procedure applies if you are using Autodetect.
And that’s all!