CODEWITHSUNDEEP
sqlgoogle-bigquery
emilk
emilk

Reputation: 106

BigQuery: cast string as datetime

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

Upvotes: 0

Views: 4448

Answers (2)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 2

Soccan
Soccan

Reputation: 35

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

Upvotes: 0

Related Questions