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