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屋！