CODEWITHSUNDEEP
google-bigquerytimestamp
ostrino
ostrino

Reputation: 33

BigQuery - Failed to parse input string

I have extracted strings from a JSON field in the following format"2020-07-0217:39:02-04:00" stored in a column called time_string.

When I use the following function PARSE_TIMESTAMP("%Y-%m-%d%H:%M:%S%Ez", time_string) I'm getting a "Failed to parse input string" error for all rows.

If I directly paste a date from time_string as the input for the parse function, I'm getting an output.

Query: SELECT PARSE_TIMESTAMP("%Y-%m-%d%H:%M:%S%Ez", "2020-07-0217:39:02-04:00")

Output: 2020-07-02 21:39:02 UTC

I know there are multiple posts with similar issues here. Read through them but was unable to figure this one out.

Upvotes: 2

Views: 5537

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

The only explanation I see is - some of your time_strings have different format than you expects

To find the, run below

#standardSQL
select time_string
from `project.dataset.table`
where SAFE.PARSE_TIMESTAMP("%Y-%m-%d%H:%M:%S%Ez", time_string) is null

Update

Looks like some of your values wrapped with quotes

enter image description here

so use below instead

select PARSE_TIMESTAMP("%Y-%m-%d%H:%M:%S%Ez", trim(time_string, '"'))

Upvotes: 3

Related Questions