STRING to DATE in BIGQUERY

Question

I am struggling to try to do this with Google BigQuery:

I do have a column with dates in the following STRING format:

6/9/2017   (M/D/YYYY)

I am wondering how can I deal with this, trying to use the DATE clause in order to get the this format: YYYY-MM-DD.

Answer 1

Easy one, with standard SQL:

#standardSQL
SELECT PARSE_DATE('%m/%d/%Y',  '6/22/2017')


2017-06-22  

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-date

Answer 2

This solution can work

SELECT    CAST(
            CONCAT(
              SUBSTR(DT_DOCUMENTO, 0 , 4), 
              '-' ,
              SUBSTR(DT_DOCUMENTO, 5 , 2), 
              '-' , 
              SUBSTR(DT_DOCUMENTO, 7 , 2) 
            ) AS DATE
          ) AS FORMAT_DATE

Answer 3

cast('06-09-2017' AS DATE FORMAT 'DD-MM-YYYY') or you can try this cast('2017/06/09' AS DATE FORMAT 'YYYY/MM/DD')

You can read more about string to date conversion with BigQuery in this link https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions

Answer 4

If you are lazy you can do date parsing with automatic format detection

select bigfunctions.eu.parse_date('1/20/21') as cleaned_date

will give

+--------------------+
| cleaned_date       |
+--------------------+
| date('2021-01-20') |
+--------------------+

as well as

select bigfunctions.eu.parse_date('Wed Jan 20 21:47:00 2021') as cleaned_date

https://unytics.io/bigfunctions/reference/#parse_date