Conversion of Strings like 'June 13, 7:23pm EDT' to Dates in BigQuery

Question

My timestamp data looks like

June 13, 7:23pm EDT

The data is in string format. I need to convert it to

2023-6-13

Is there a way to do that? I am using BigQuery.

If I do:

select cast('June 13, 7:23pm EDT' as date);

I get this error:

Invalid date: 'June 13, 7:23pm EDT'

Answer 1

Your cast query is correct but I believe the source timestamp formatting cannot match the cast's accepted format. The closest I can get is using parse_date function. But I have extracted the the date only using string manipulation(ragexp_contains) and extract the year to current date to full fill the formatting of the parse date paramter.

SELECT
  PARSE_DATE('%b %e %Y', CONCAT(REGEXP_EXTRACT('June 13, 7:23pm EDT', r'^(.*?),'), ' ', EXTRACT(YEAR
      FROM
        CURRENT_DATE()))) AS date

It is possible that there is a more optimal approach to this but here is the ouput of the query: