timstamp from string to timestamp in BigQuery

Question

i have the followoing format of a timestamp which is saved as string.

2022-09-15T11:07:28.872

I've tried to convert it to timestamp like this

PARSE_TIMESTAMP("%Y-%m-%d-%H:%M:%S",JSON_VALUE(DATA,'$.payload.onHoldUntil'))

but i get the following error

Mismatch between format character '-' and string character 'T'

How can i solve this issue ?

Answer 1

The format string you provide in the PARSE_TIMESTAMP function should align to the format of your string. In your case it does not as you are missing a T and the milliseconds.

Try the following:

select '2022-09-15T11:07:28.872' as s_ts
  , PARSE_TIMESTAMP('%FT%R:%E3S', '2022-09-15T11:07:28.872')