How to treat and transform a STRING with very complex date format into DATETIME format using SQL (BigQuery)

Question

Within Google BigQuery (SQL) I have a table called Sales, with a column in STRING format called data_pt_filtro that has dates, but this column is all dirty and needs treatment.

Below are some samples I took from this column:

1/1/2019 - 9:5
20/2/2019 - 10:2
9/7/2020 - 10:30
14/1/2020 - 17:46
28/10/2019 - 17:35
18/9/2019 - 9:50
28/1/2020 - 11:6
2019-07-21
2019-8-21
2019-8-15
2019-11-16
04/12/2019
27/03/2020
9/4/2020
09/6/2020 - 11:41
(error)
xxxxxifhifhuih
NULL
wwrwgghth
22/01

A part of it is in the Brazilian format and even then it is not standardized.

1. This Brazilian format:

dd/mm/yyyy - hh:mm

But it is not standardized.

For example, the date of: Day 1 of January of 2019, 9:05 in this column is like this = 1/1/2019 - 9:5 What makes it difficult, it would be easier if it were like this = 01/01/2019 - 09:05

And it doesn't always have the time…

2. Another part is in American format:

This format:

yyyy-mm-dd

How do I treat this column and transform this entire column to DATETIME format?

NOTE: when you do not have the hours, set the hours as 00:00:00 by default and when it has a value that does not mean a date, put it as a NULL value.

Answer 1

If multiple formats of datetime are mixed up in a single column, you can apply a format string one by one until you find a proper format to parse it. If failed to find a right format string, it will end up return null.

WITH sample_table AS (
  SELECT * FROM UNNEST([
    '1/1/2019 - 9:5',
    '20/2/2019 - 10:2',
    '9/7/2020 - 10:30',
    '14/1/2020 - 17:46',
    '28/10/2019 - 17:35',
    '18/9/2019 - 9:50',
    '28/1/2020 - 11:6',
    '2019-07-21',
    '2019-8-21',
    '2019-8-15',
    '2019-11-16',
    '04/12/2019',
    '27/03/2020',
    '9/4/2020',
    '09/6/2020 - 11:41',
    '(error)',
    'xxxxxifhifhuih',
    'NULL',
    'wwrwgghth',
    '22/01'
  ]) str
)
SELECT str,
       COALESCE(
         SAFE.PARSE_DATETIME('%d/%m/%Y - %H:%M', str),
         SAFE.PARSE_DATETIME('%d/%m/%Y', str),
         SAFE.PARSE_DATETIME('%Y-%m-%d', str)
       ) AS dt
  FROM sample_table;

Query results

• See also
  • format element
  • SAFE. prefiex - How to safely convert strings to date?