to_gbq fails when trying to save a Pandas datetime64[ns] to a BQ DATE field

[swt2c]

Traceback (most recent call last):
  File "/tmp/test-bq_20210416_36390b15/test_bq.py", line 8, in <module>
    df.to_gbq(
  File "/opt/conda/default/lib/python3.8/site-packages/pandas/core/frame.py", line 1710, in to_gbq
    gbq.to_gbq(
  File "/opt/conda/default/lib/python3.8/site-packages/pandas/io/gbq.py", line 211, in to_gbq
    pandas_gbq.to_gbq(
  File "/opt/conda/default/lib/python3.8/site-packages/pandas_gbq/gbq.py", line 1093, in to_gbq
    connector.load_data(
  File "/opt/conda/default/lib/python3.8/site-packages/pandas_gbq/gbq.py", line 580, in load_data
    self.process_http_error(ex)
  File "/opt/conda/default/lib/python3.8/site-packages/pandas_gbq/gbq.py", line 380, in process_http_error
    raise GenericGBQException("Reason: {0}".format(ex))
pandas_gbq.gbq.GenericGBQException: Reason: 400 Error while reading data, error message: Could not parse '2021-04-17 00:00:00.000000' as DATE for field for_date (position 1) starting at location 0  with message 'Unable to parse'

I seem to be able to work around this by forcing the datetime64[ns] column to the .dt.date of the datetime64, but it seems like pandas-gbq should be able to figure this out when I supply the schema with a 'DATE' column in it.

[tebesfinwo]

I, too, encountered the same problem. I also tried to state schema explicitly like the following but to no avail.

pandas_gbq.to_gbq(df, "table", "project_id", table_schema=[{"name": "foo", "field": "DATE"}])

[RomikimoR]

I am facing the exact same situation with a byte field.

[tswast]

I recently did some work with the db-dtypes package, which has a well-defined serialization in parquet (which is now the default). I'll have to do some investigation to see what it does with CSV serialization.

I agree that when the table_schema is provided, we could use that as a hint when serializing the data to write to BigQuery.

[tswast]

Looks like this was fixed by #413, but I'll keep this open while I add some system tests for this issue.