Invalid date error in custom query in Data Studio

4
votes

I am trying to prepare a chart using custom query (BigQuery) in Data Studio.However I get invalid date error when using Data Studio Date Parameter @DS_START_DATE and @DS_END_DATE. Here is my query

SELECT cat_tbl.*,tag.Category_name 
FROM 
   (SELECT 
     (SELECT category FROM UNNEST(ana_cat) ORDER BY score DESC LIMIT 1) AS category,
     * 
   FROM `projectId.dataset.table1`) AS cat_tbl  
   RIGHT JOIN `projectId.dataset.table2` AS tag 
     ON cat_tbl.category=tag.Category_id
WHERE DATE(cat_tbl.date) BETWEEN @DS_START_DATE AND @DS_END_DATE

cat_tbl.date is DATETIME field. When I run this I get following error

Invalid date: '20191014'

Error ID: 853185df

I am new to BigQuery and Data Studio.Any advice would be really helpfull.

1
google-bigquerygoogle-data-studio

1 Answers

8
votes

If you look at the example from the Data Studio blog post, those query parameters aren't really dates (somewhat confusingly) but are strings in the format YYYYMMDD. So you need to parse them as dates in your query:

SELECT cat_tbl.*,tag.Category_name 
FROM 
   (SELECT 
     (SELECT category FROM UNNEST(ana_cat) ORDER BY score DESC LIMIT 1) AS category,
     * 
   FROM `projectId.dataset.table1`) AS cat_tbl  
   RIGHT JOIN `projectId.dataset.table2` AS tag 
     ON cat_tbl.category=tag.Category_id
WHERE DATE(cat_tbl.date)
    BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND
    PARSE_DATE('%Y%m%d', @DS_END_DATE)