snowflake-cloud-data-platform - 时间戳无法识别雪花

Question

我在snowflake中有一个字符串值如下；

"2018-03-08T22:43:23.848Z"

"2018-03-19T18:56:02.886Z"

"2018-03-06T12:39:57.432Z"

正在尝试使用以下代码将 I 转换为时间戳格式；

TO_TIMESTAMP(col_name::VARCHAR)

我使用 VARCHAR 来去掉双引号。但是出现以下错误；

Timestamp '{"dayOfMonth":28,"dayOfWeek":"WEDNESDAY","dayOfYear":87,"hour":3,"minute":13,"month":"MARCH","monthValue":3,"nano":493000000,"offset":{"id":"Z","rules":{"fixedOffset":true,"transitionRules":[],"transitions":[]},"totalSeconds":0},"second":17,"year":2018}' is not recognized

如果我的语法有误或有更好的方法来处理此错误，请提出建议。

Answer 1

使用显式格式:

SELECT TRY_TO_TIMESTAMP(col_name::VARCHAR, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM tab