Date field type changes the data values

Get Help
1

Hey,

I have a registration date field, due to a trouble in the BigQuery external table reading this field from the files, we set this field to be a string in BQ table schema.

image
image144×515 1.61 KB

When working with this table in Metabase, and setting this field type to be a date type it changes the field values in an inconsistent way (pic below) when some values are 27/09/2022 and some are February 10, 2022, 12:00 AM.
reg date as a date

It won't allow me to group by or to create filters on this field

Any ideas on how can I work around this?
Thanks in advance!

Error:
400 Bad Request POST https://www.googleapis.com/bigquery/v2/projects/pdp-prod/queries { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "Invalid datetime string "06/10/2022"", "reason" : "invalidQuery" } ], "message" : "Invalid datetime string "06/10/2022"", "status" : "INVALID_ARGUMENT" }

2

Hi @Yinon.Eitan
Your data is in string format and different ways it is represented, so you need to clean up your data, so it's all in the same uniformed format - preferably YYYY-MM-DD - so computers can handle it.

3

Hey,
thanks a lot for answering. The data is indeed in a string format but its all in the same format. only in Metabase when I change the type to 'date' it changes to be like in the second picture.

In BQ and as a string it the same format

4

@Yinon.Eitan Computers have a really hard time processing non-strict formats. I would recommend that you create a View on your database, so you convert your strings to a real DATE type or at least a computer format like YYYY-MM-DD, which is a known ISO standard.

1 Like