数据洞察中自定义查询中的无效日期错误

问题描述

我正在尝试使用 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.

推荐答案

如果您查看 Data Studio 博客文章，这些查询参数并不是真正的日期(有点令人困惑)，而是 YYYYMMDD 格式的字符串.因此，您需要将它们解析为查询中的日期:

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)

这篇关于数据洞察中自定义查询中的无效日期错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！