How to convert any date format and date time format into a particular date format using Big Query?

Question

I have a Date column with different date format and datetime format. Now, I want to convert the format into dd/mm/yyyy. I used combination of PARSE_TIMESTAMP,FORMAT_TIMESTAMP and FORMAT_DATE. But the query I wrote is throwing error. How do I query in Big query so the update statements can convert any kind of dateformat and datetime format ?

My data:-

2020/12/23
2020-03-24
20200524
07/30/2020
09-30-2021
09/20/20
12-24-20
20/09/2020
22-09-2023
09-2023
10-20
Jan2020
jan-2020
2020-jun
2020.12.22
12.22.2020
2020/04/22 12:30:09
2023-09-24 11:30:20
2022/09/23 11:20:30.22
2020-12-20 11:24:30.02
20220923141530 (September 23, 2022, 14:15:30)
09/23/2022 14:15:30
09-23-2022 14:15:30

Big Query:-

UPDATE `project.dataset.table`
SET Date =
  CASE 
    WHEN PARSE_TIMESTAMP('%Y/%m/%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d', Date))
    WHEN PARSE_DATE('%Y%m%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y%m%d', Date))
    WHEN PARSE_DATE('%m/%d/%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/%Y', Date))
    WHEN PARSE_DATE('%m-%d-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-%Y', Date))
    WHEN PARSE_DATE('%m/%d/yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/yy', Date))
    WHEN PARSE_DATE('%m-%d-yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-yy', Date))
    WHEN PARSE_DATE('%d/%m/%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d/%m/%Y', Date))
    WHEN PARSE_DATE('%d-%m-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', Date))
    WHEN PARSE_DATE('%m-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%Y', Date))
    WHEN PARSE_DATE('%m-yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-yy', Date))
    WHEN PARSE_DATE('%b%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b%Y', Date))
    WHEN PARSE_DATE('%b-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b-%Y', Date))
    WHEN PARSE_DATE('%Y-%b', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y-%b', Date))
    WHEN PARSE_DATE('%Y.%m.%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y.%m.%d', Date))
    WHEN PARSE_DATE('%m.%d.%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m.%d.%Y', Date))
    WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date))
    WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date))
    WHEN PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date))
    WHEN PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date))
    ELSE NULL
  END
WHERE TRUE;

Error:-

Mismatch between format character '/' and string character '-'

Answer 1

You are seeing the error because your case when statements are evaluating each row of data. So using your example list, the 1st item 2020/12/23 is evaluated as true by the first case when (and the rest of the case statement is short-circuited). However, the 2nd item 2020-03-24 fails the first case when with the given error. You can confirm this with the following:

-- This won't work
select PARSE_TIMESTAMP('%Y/%m/%d', '2020-03-24')

You should probably consider using a regex function in your case statement to test the input format before you parse.

Answer 2

If you are getting the error Mismatch between format character '/' and string character '-', it means that the input date string does not match the expected format. You can try to fix this by changing the format string in the query, or by correcting the input data.

Here are some tips for handling errors when converting date and datetime formats in BigQuery:

Use the TRY() function to catch and handle errors. Use the SAFE_PARSE_DATE() function to parse date strings that may contain errors. Use the SAFE_CAST() function to cast date and datetime strings to the appropriate data types. For more information on date and datetime functions in BigQuery, please see the reference documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions.