Big Query Multiple Date Format-CodePudding

I have a table that have multiple date format on big query table. The Date format is followed: (please note, this is coming from staging table so the data type is defaulted as string type.

mm/dd/yyyy
yyyy-mm-dd

I'm trying to standardized date format to yyyy-mm-dd. Here is the query as followed:

select  calendar_date, 
        FORMAT_DATE('%Y-%m-%d',safe_cast(calendar_date as date))
        FROM `calendar_dates.table`

The result that came out as null

please let me know if there is another way to solve this.

CodePudding user response：

Use below

SELECT
  calendar_date,
  COALESCE(SAFE.PARSE_DATE('%Y-%m-%d', calendar_date), SAFE.PARSE_DATE('%d/%m/%Y', calendar_date))
FROM `calendar_dates.table`

CodePudding user response：

The below query, that uses safe.parse_date seems to work for me:

WITH stg AS (
  SELECT "9/9/2001" AS input_date
  UNION ALL
  SELECT "2021-01-01" AS input_date)

SELECT
  CASE
    WHEN safe.parse_DATE("%Y-%m-%d", input_date) IS NULL THEN 
safe.parse_DATE("%d/%m/%Y", input_date)
    ELSE safe.parse_DATE("%Y-%m-%d",input_date)
  END AS output_date
FROM
  stg

The table

becomes:

The logic is that: BQ tests one format and if it cannot make any sense of it, it tests the other format. If none of them work the result is NULL.