CODEWITHSUNDEEP
sqlgoogle-bigquery
fajri kemal
fajri kemal

Reputation: 13

How to Convert STRING to DATETIME in BIG QUERY

I Have TS column in data table which contains String Like this '20220205183013', I want to convert it to datetime format like this 2022-02-05 18:20:18 UTC.

I have tried this query but failed.

select CAST(ts AS TIMESTAMP 'YYYY-MM-DD HH24:MI:SS') as data_k from data_

and

select PARSE_DATETIME("%a %b %e %Y %I:%M:%S",ts)  from data_

but it still failed.

Upvotes: 1

Views: 6855

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

You could use PARSE_TIMESTAMP here with the correct format mask:

SELECT PARSE_TIMESTAMP('%Y%m%d%H%M%S', '20220205183013');

The mask you were using with PARSE_DATETIME does not match the input timestamp, which is in the format %Y%m%d%H%M%S.

Upvotes: 2

Related Questions