TO_UTC_TIMESTAMP_TZ takes a ISO8601 formatted date string and converts it to a TIMESTAMP WITH TIMEZONE datatype.
According to documentation this is the ebnf of to_utc_timestamp_tc
The varchar parameter may be either a date (only) or a date with time combination. The format of the parameter is:
Date only:
YYYY-MM-DD
Date / time:
YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]]][Z|(+|-)hh:mm]
Example
with data (d) as (select '2020-02-28T21:32:52' from dual union all select '2020-02-28T21:32:52+02:00' from dual union all select '2020-02-28T19:32:52Z' from dual union all select '2020-02-28T19:32:52+00:00' from dual union all select '2020-02-28T21:32:52.12679' from dual union all select '2020-02-28') select to_utc_timestamp_tz(d) as converted from data; CONVERTED --------------------------------------------- 28.02.2020 21:32:52.000000000 GMT 28.02.2020 21:32:52.000000000 +02:00 28.02.2020 19:32:52.000000000 GMT 28.02.2020 19:32:52.000000000 GMT 28.02.2020 21:32:52.126790000 GMT 28.02.2020 00:00:00.000000000 GMT 6 rows selected.
DEFAULT RETURN VALUE ON CONVERSION ERROR
As most of the other conversion functions documentation says that TO_UTC_TIMESTAMP_TZ supports the on conversion error clause too. But when trying to use it you will get an error.
with data (d) as (select '2018-10-26T21:32:52' from dual union all select '2018-10-26T21:32:52+02:00' from dual union all select '2018-10-26T19:32:52Z' from dual union all select '2018-10-26T19:32:52+00:00' from dual union all select '2018-10-26T21:32:52.12679' from dual) select to_utc_timestamp_tz(d default null on conversion error) converted from data; ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 11 Column: 30
Documentation Bug
I have filed an SR with Oracle Support regarding this error. It has been classified as documentation bug:
DOC 30936692 – TAKE OUT “[ DEFAULT RETURN_VALUE ON CONVERSION ERROR ]” OPTIONAL CLAUSE FROM TO_UTC_TIMESTAMP_TZ
and will be removed from future (>20) documentations.
Conclusion
Somehow I hopped, that Oracle would add the missing functionality concerning the conversion error handling to the TO_UTC_TIMESTAMP_TZ function rather than declaring it as a documentation bug.
Not having a decent way to handle exceptions (besides building a user defined function with an exception handler) will keep me away from using this function.