'Big Query Multiple Date Format

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

enter image description here

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

sqlgoogle-bigquerybigdata


Solution 1:[1]

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`

Solution 2:[2]

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

enter image description here

becomes:

enter image description here

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant
Solution 2 Cylldby

Related Questions