I need some help around BigQuery date formatting based on their values. The source data has STRING datatype and target datatype is DATE. I need to format the input dates based on their values as follow:

1. NULL value should remain NULL
2. Empty string("") should be convert to NULL
3. Date with YYYY-MM-DD format should remain as is
4. Date with MM/DD/YYYY format should be convert to YYYY-MM-DD format

Here's what I have done so far:

SELECT input_date, CASE WHEN input_date = '' THEN NULL ELSE PARSE_DATE('%m/%d/%Y', input_date) END AS output_date FROM mytable

The above case statement fails when try to parse the dates with YYYY-MM-DD format. Here's the error I am getting:

How do I solve for the YYYY-MM-DD date format? Any feedback is appreciated.

CodePudding user response：

You may try and consider below approach wherein you will need to add another WHEN and then use regex to match the YYYY-MM-DD string and then parse it to your desired date format as shown below.

with sample_data as (
  select NULL as input_date,
  union all select '' as input_date,
  union all select '2022-05-21' as input_date,
  union all select '05/25/2022' as input_date
)
SELECT input_date, 
  CASE WHEN input_date = '' THEN NULL
  WHEN REGEXP_CONTAINS(input_date, r'^\d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])$') THEN PARSE_DATE('%F', input_date)
  ELSE PARSE_DATE('%m/%d/%Y', input_date) END AS output_date FROM sample_data

Output:

CodePudding user response：

Consider below query:

WITH sample_data AS (
  SELECT * FROM UNNEST(['', null, '2022-05-21', '05/25/2022']) input_date
)
SELECT *, 
       COALESCE(SAFE.PARSE_DATE('%m/%d/%Y', input_date), SAFE.PARSE_DATE('%F', input_date)) output_date
  FROM sample_data;

Output results