Tags

,

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

to_utc_timestamp_tz

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.