How to make connector compatible with new Date / Date and Time types

user_2415037 0
Viewed 433

We have a community connector which makes us of Data Studio Advanced Services to run a query on a specific BigQuery dataset.

Since the new Date type was introduced on 15th September 2020, any reports including a date or date and time type will fail.

From the logs, the query that is being executed includes a section like:

PARSE_DATE('%Y%m%d', SAFE_CAST(t0.updated AS STRING)) AS t0_qt_bthq67ilcc

where "updated" is the name of the field of the type DATE.

When this query is executed BigQuery is reporting Failed to parse input string "2020-09-23".

Any data sources that were created before 15th September are using the compatibility mode, which are continuing to work fine.

The community connector includes setUseStandardSql(true) so I don't believe it is using Legacy SQL.

What can we do to add compatibility for this new date type?

Thanks in advance!

google-data-studio
1 Answers

I've been able to make the connector compatible.

To resolve this I had to edit the SQL query to FORMAT_DATE("%Y%m%d", fieldName) AS fieldName.

If the field is a timestamp it's FORMAT_TIMESTAMP("%Y%m%d%H%M%S", fieldName) AS fieldName. Just posting in case anyone else comes across this issue.

user_24150370
Related