Data Studio中自定义查询中的无效日期错误 [英] Invalid date error in custom query in Data Studio
问题描述
我正在尝试使用Data Studio中的自定义查询(BigQuery)准备图表.但是,使用Data Studio日期参数@DS_START_DATE
和@DS_END_DATE
时出现无效的日期错误.这是我的查询
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
是DATETIME
字段.
当我运行此程序时,出现以下错误
cat_tbl.date
is DATETIME
field.
When I run this I get following error
Invalid date: '20191014'
Error ID: 853185df
我是BigQuery和Data Studio的新手,任何建议都会很有帮助.
I am new to BigQuery and Data Studio.Any advice would be really helpfull.
推荐答案
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)
这篇关于Data Studio中自定义查询中的无效日期错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!