JSON documents and REST services exchange dates and times as ISO 8601 strings, such as 2026-03-15T08:30:00+04:00 or 2026-03-15T08:30:00Z. Converting them with TO_TIMESTAMP_TZ needs a format model that matches the T, the fraction, and the offset exactly. TO_UTC_TIMESTAMP_TZ reads ISO 8601 strings directly, with no format model, and returns a TIMESTAMP WITH TIME ZONE.
Code for This Guide
The main example is in the examples/conversion-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
to_utc_timestamp_tz(varchar2)
The argument is an ISO 8601 date or timestamp: a date alone (YYYY-MM-DD), or a date and time joined by T, optionally with fractional seconds and with an offset such as +04:00 or the letter Z for UTC.
Convert ISO 8601 Strings
Example:
select to_utc_timestamp_tz('2026-03-15T08:30:00+04:00') as from_offset,
to_utc_timestamp_tz('2026-03-15T08:30:00Z') as from_utc,
to_utc_timestamp_tz('2026-03-15') as date_only
from dual;Output:
FROM_OFFSET FROM_UTC DATE_ONLY ______________________________ ___________________________ ___________________________ 15-MAR-2026 08:30:00 +04:00 15-MAR-2026 08:30:00 GMT 15-MAR-2026 00:00:00 GMT
A string with an offset keeps that offset. Z means UTC, shown as GMT. A date alone becomes midnight UTC.
Get the UTC Time
Despite its name, the function does not move the value to UTC: 08:30 +04:00 stays 08:30 +04:00, which is the same moment as 04:30 UTC. Apply SYS_EXTRACT_UTC when you need the UTC clock time, for example to store it in a TIMESTAMP column without a time zone.
Example:
-- the result keeps the offset of the string; SYS_EXTRACT_UTC gives the UTC time
select to_utc_timestamp_tz('2026-03-15T08:30:00+04:00') as with_offset,
sys_extract_utc(to_utc_timestamp_tz('2026-03-15T08:30:00+04:00')) as in_utc,
to_utc_timestamp_tz('2026-03-15T08:30:00.250-05:00') as with_fraction
from dual;Output:
WITH_OFFSET IN_UTC WITH_FRACTION ______________________________ _______________________ ______________________________ 15-MAR-2026 08:30:00 +04:00 15-MAR-2026 04:30:00 15-MAR-2026 08:30:00 -05:00
Fractional seconds are accepted; the display shows them only if the session's timestamp format includes them.
Non-ISO Strings Fail
A string in another layout raises an error, so use TO_TIMESTAMP_TZ with a format model for those.
Example:
select to_utc_timestamp_tz('15/03/2026 08:30') as not_iso from dual;Output:
Error starting at line : 1
In command -
select to_utc_timestamp_tz('15/03/2026 08:30') as not_iso from dual
Error at Command Line : 1 Column : 64
Error report -
SQL Error: ORA-01830: Date format picture ends before converting entire input string.Related Guides
- Oracle TO_TIMESTAMP_TZ Function: A Simple Guide
- Oracle SYS_EXTRACT_UTC Function: A Simple Guide
- Oracle Datetime Format Models Explained
Conclusion
TO_UTC_TIMESTAMP_TZ converts ISO 8601 date and timestamp strings into TIMESTAMP WITH TIME ZONE without a format model, reading offsets and Z for UTC, and treating a date alone as midnight UTC. The result keeps the string's offset; wrap it in SYS_EXTRACT_UTC to get the UTC time.
