[BigQuery] Regarding Conversion String to Date Type Issue

Google Cloud Data Analytics
4

The error you’re encountering indicates that the format string doesn’t correctly match the timestamp format. Let’s refine the format string further. Given your timestamp format: ‘2024-06-22T19:00:01+00.000Z’ Here’s the correct approach:

%Y-%m-%d: Date part T%H:%M:%S: Time part %Ez: Timezone part (including milliseconds) Let’s adjust the format string:

SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S%E*1S%Ez', '2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S%E*1S%Ez', '2024-06-22T19:00:01+00.000Z')) AS date;

Explanation:

  • %Y-%m-%d: Year, month, day
  • T%H:%M:%S: Hours, minutes, seconds
  • %E*1S: Optional fractional seconds (milliseconds) with one or more digits
  • %Ez: Timezone offset

Alternatively, if the above does not work, you can handle the timestamp using string manipulation functions to ensure it’s parsed correctly. Here’s a different approach:

SELECT TIMESTAMP('2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(TIMESTAMP('2024-06-22T19:00:01+00.000Z')) AS date;

If you want to apply this to a column in your table:

SELECT TIMESTAMP(your_timestamp_column) AS timestamp_column,
       DATE(TIMESTAMP(your_timestamp_column)) AS date_column
FROM your_table;

This approach leverages the TIMESTAMP function directly, which is capable of handling ISO 8601 format timestamps including timezone and milliseconds. This simplifies the conversion without needing a complex format string.

1 Like