BigQuery: cast string as datetime-CodePudding

I have a column with millions of dates called 'timestamp' in bigquery, but they are marked as string.

The format of the timestamp column is as follows:

2022-02-09T12:01:51.911821Z

for every observation in the dataset.

I wish to convert it to datetime and have tried the following:

SELECT
  CAST(timestamp AS DATETIME)
FROM
  `bigquery_table`
LIMIT
  1000

The query will process but I am told that 'Invalid datetime string "2022-02-09T12:01:51.911821Z"'. Any suggestions to solve this?

Thanks

CodePudding user response：

That might be ISO 8601 format, you can use PARSE_TIMESTAMP function.

SELECT
  PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', timestamp)
FROM
  `bigquery_table`
LIMIT
  1000

CodePudding user response：

You can try to first cast it as datetimeoffset as per: select cast(cast('2022-02-09T12:01:51.911821Z' as datetimeoffset) as datetime)