在bigquery中解析日期形式的字符串 [英] parse date form string in bigquery

问题描述

我在bigquery中有一个表，该表的列名称为"timeStamp"，数据类型为STRING.该列中的值看起来像这样的"20180902".

I have a table in bigquery that has a column name "timeStamp" with data type of STRING. The value in this column looks like this "20180902".

这就是我试图将此字符串解析为最新日期的方式

This is how I am trying to parse this string to date

SELECT PARSE_DATE('%Y%m%d', timeStamp) AS date FROM data.data_temp

但这会引发错误，提示无法解析输入字符串"timeStamp""

But this throws an error that says "Failed to parse input string "timeStamp""

此外，以下查询同时成功运行，并将日期返回为"2018-09-02"

Also, at the same time the following query works successfully and returns the date as "2018-09-02"

SELECT PARSE_DATE('%Y%m%d', '20180902') AS date FROM data.data_temp

关于这里发生的事情以及如何解决这个问题的任何线索吗?

Any clues on what is going on here and how to solve this?

推荐答案

下面的示例显示，如果您声明值确实为"20180902"，则该值应该有效

Below example shows that if value is really "20180902" as you state it should work

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '20180902' timeStamp   
)
SELECT 
  `timeStamp`, 
  PARSE_DATE('%Y%m%d', timeStamp    ) AS date    
FROM `project.dataset.table`

结果为

Row   timeStamp   date   
1     20180902    2018-09-02     

因此，该问题在某些行中可能具有错误的值-并且您需要标识这些行

so the issue potentially in some wrong value in some rows - and you need to identify these row(s)

这篇关于在bigquery中解析日期形式的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！