datetimegoogle-cloud-platformgoogle-bigquerydatetime-formatformatdatetime

How to convert any type of date format and datetime format into a particular date format using Big Query?

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 TIMESTAMP_DIFF,PARSE_TIMESTAMP,FORMAT_TIMESTAMP and FORMAT_DATE. Though it able to convert all but throws error at date time format like dd/mm/yy HH:MM. How do I query in Big query so the update statements can convert any kind of dateformat and datetime format ?

    data:-
0           21/12/2006
1           25/01/2007
2           20/02/2023
3           1996.12.25
4           24/12/2020
5           24/12/2020
6     24/05/2020 12:02
7     24/05/2020 12:02
8     24/05/2020 12:02
9     24/05/2020 12:02
10          20/02/2023
11          21/02/2023
12          22/02/2023
13          23/02/2023
14          24/02/2023
15          25/02/2023
16          26/02/2023
17          02/12/2020
18          02/23/2020
19          09/23/2020
Name: Date, dtype: object
Big query:-
UPDATE table_name
SET
   Date = CASE
    WHEN TIMESTAMP_DIFF(PARSE_TIMESTAMP('%d/%m/%Y', Date), TIMESTAMP_TRUNC(PARSE_TIMESTAMP('%d/%m/%Y', Date), DAY), SECOND) > 0 THEN
      FORMAT_TIMESTAMP('%d%m%y', PARSE_TIMESTAMP('%d/%m/%Y', Date))
    ELSE
      FORMAT_DATE('%d%m%y', PARSE_DATE('%d/%m/%Y', Date))
  END
WHERE TRUE;

Error:
Failed to parse input string "24/05/2020 12:02"
Solution

  • The error you are encountering is because the PARSE_TIMESTAMP was unable to recognize the format of the input. To solve this error you can consider using "regex" or “SAFE” keywords inside the case query. The “SAFE” keyword returns NULL when it sees the bad data or data which is not in the expected format for conversion.

    Example:

    select
CASE
    WHEN SAFE.PARSE_TIMESTAMP('%d/%m/%Y', Dates) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%d/%m/%Y', Dates))
    WHEN SAFE.PARSE_TIMESTAMP('%Y.%m.%d', Dates) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y.%m.%d', Dates))
    WHEN SAFE.PARSE_TIMESTAMP('%d/%m/%Y %H:%M', Dates) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%d/%m/%Y %H:%M', Dates))
    ELSE   ---Add more when statement to match more date value
      NULL  
  END from t1;

    RESULT:

    image

    For more information you can refer to these link1 and link2.