BigQuery - Failed to parse input string

Question

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.

Mikhail Berlyant · Accepted Answer · 2020-10-23 03:44:25Z

3

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

so use below instead

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

edited Oct 23, 2020 at 3:44

answered Oct 23, 2020 at 3:08

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ostrino Over a year ago

I ran your query and I'm getting all the rows that were there in my table as the output. The ````PARSE_TIMESTAMP``` function doesn't appear to be working for any value. screenshot

Mikhail Berlyant Over a year ago

please give an example of at least one time_string value that came out!

ostrino Over a year ago

Sample time_string value: "2020-09-2222:36:27-07:00" Like I said in my initial question, if I directly input this in the parse_timestamp function I'm getting the output I'm looking for.

Mikhail Berlyant Over a year ago

are you sayin that you run the query I provided in my answer and value 2020-09-2222:36:27-07:00 came out? please confirm

Mikhail Berlyant Over a year ago

so, i checked the screenshot you provided in your comment (i missed it initially) - so, don't you see that the values that came out are of different format - which is - they all have extra quotes at the beginning and end - so use PARSE_TIMESTAMP("%Y-%m-%d%H:%M:%S%Ez", trim(time_string, '"')) instead

|

Collectives™ on Stack Overflow

BigQuery - Failed to parse input string

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related