Extract date from timestamp containing time zone in Big Query-CodePudding

I have data containing dates of the form

2020-12-14T18:58:10 01:00[Europe/Stockholm]

but I really only need the date 2020-12-14. So, I tried:

DATE(Timestamp) as LastUpdateDate

which returned Error: Invalid time zone: 02:00[Europe/Stockholm]

So, thinking that the problem came from the time zone, I tried this instead:

TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d", PARSE_TIMESTAMP("%Y%m%d", Timestamp)))

which magically returned a new error, namely

Error: Failed to parse input string "2021-10-04T09:24:20 02:00[Europe/Stockholm]"

How do I solve this?

CodePudding user response：

Just substring the date part from the string. Try one of these:

select left(Timestamp, 10)
select date(left(Timestamp, 10))

CodePudding user response：

You should clean your data first.

select date("2020-12-14T18:58:10 01:00") as LastUpdateDate

This will work as expected.

Any chance of cleaning your data before using it in a query? Actually I think that 01:00[Europe/Stockholm] is not supported as format.