Search code examples
datecastinggoogle-bigquery

Convert String to Date in Big Query error

I have a string field that is being pulled from a table and I'm trying to cast that as a date in a view I created. I keep getting an error when trying to Cast as a date though. The format of the field looks like this:

July 19, 2020 or August 8, 2020 etc..

I get an error that states

"Failed to parse input string "July 19, 2020"

or one of the other dates in the data when trying to use DATE_Parse. Or I get

"Invalid Date:August 8, 2020"

if I try to use the CAST function.

Below is my query when trying to CAST the date:

select
noteattributes.value.name as name_type, noteattributes.value.value as name_value, CAST(noteattributes.value.value as DATE) as DATE_TEST, order_number
from test.orders,
unnest(note_attributes) as noteattributes
where noteattributes.value.name = 'Pickup-Date'
Solution

  • Convert String to Date

    Below is for BigQuery Standard SQL

    You should use PARSE_DATE instead of CAST as in below example

    PARSE_DATE('%B %d, %Y', date_as_string)

    You can test, play with this using example below

    #standardSQL
WITH `project.dataset.table` AS (
  SELECT 'July 19, 2020' date_as_string UNION ALL
  SELECT 'August 8, 2020'
)
SELECT PARSE_DATE('%B %d, %Y', date_as_string) AS date_as_date
FROM `project.dataset.table`

    with output

    Row date_as_date     
1   2020-07-19   
2   2020-08-08