I have a Date in a String format and I can't convert it to date in BigQuery

Question

I started with a date in a string format from a JSON extraction using this: json_value(answer, '$.date_created') and got an output 2020-01-02T10:26:47.056-04:00.

From there, I transformed the output (because I couldn't change it to date using a series of functions like regexp_replace, left and CAST) to a date-like string: 2020-01-02 10:26:47

I need to be able to transform this new string to a date. So far, I've tried with FORMAT_DATETIME and FORMAT_TIMESTAMP but I'm getting an error: Failed to parse input string bigquery

Answer 1

Your original timestamp string is just fine to do this:

select Date(timestamp("2020-01-02T10:26:47.056-04:00"))

Only thing here to check is: you have -4 offset from UTC so as long as you take care of timezone etc, above style should work fine.