Error converting strimg column to timestamp in BigQuery

Question

I am getting the following error:

Invalid datetime string "2021-07-29T01:06:04.636651000+01:00"

trying to convert a string from nested json into a new datetime column. I tried using PARSE_TIMESTAMP as I thought per the documentation:

SELECT PARSE_TIMESTAMP("%G-%m-%dT%H:%M:%E9S%Ez", JSON_VALUE(event, '$.timestamp'))

but keep getting the error so obviously something is not quite right but not sure what.

Answer 1

Can you try it this way? There's an error with the format.

SELECT PARSE_TIMESTAMP("%G-%m-%dT%H:%M:%E6S%Ez", JSON_VALUE(event, '$.timestamp'))

Answer 2

The second section was causing the problem the fix was to use a wildcard on length:

SELECT PARSE_TIMESTAMP("%G-%m-%dT%H:%M:%E*S%Ez", JSON_VALUE(event, '$.timestamp'))