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