Search code examples
sqlgoogle-bigquerycastingdate-formatting

Date format change in bigquery

I have a date 12-04-2023 22:30 as a string and need to convert it like 2023-04-11 17:32:38.171728 UTC format or vice-versa. Any help is appreciated.

Tried parse_datetime function and cast function. Doesn't help

Solution

  • First conversion: 12-04-2023 22:30 to 2023-04-11 17:32:38.171728 - Note I noticed the to be converted date is different to input date so considering the format here:

    SELECT FORMAT_TIMESTAMP("%F %R:%E6S",PARSE_DATETIME("%d-%m-%E4Y %H:%M", "12-04-2023 22:30"))  as formated_date

    Second conversion: this format 2023-04-11 17:32:38.171728 to 12-04-2023 22:30

    SELECT FORMAT_TIMESTAMP("%d-%m-%Y %H:%M","2023-04-12 22:30:00.000000")   as formated_date

    Hope this helps