International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates, times, and durations. Oracle Database supports the most common ISO 8601 formats as proper Oracle SQL date, time, and interval (duration) values. The formats that are supported are essentially those that are numeric-only, language-neutral, and unambiguous.
(Simple Oracle Document Access (SODA) does not support durations.)
Oracle Database Syntax for ISO Dates and Times
This is the syntax that Oracle Database supports for ISO dates and times:
Date with time:
YYYYspecifies the year, as four decimal digits.
MMspecifies the month, as two decimal digits,
DDspecifies the day, as two decimal digits,
hhspecifies the hour, as two decimal digits,
mmspecifies the minutes, as two decimal digits,
ss[.s[s[s[s[s]]]]]specifies the seconds, as two decimal digits,
59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
Zspecifies UTC time (time zone 0). (It can also be specified by
+00:00, but not by
(+|-)hh:mmspecifies the time-zone as difference from UTC. (One of
For a time value, the time-zone part is optional. If it is absent then UTC time is assumed.
No other ISO 8601 date-time syntax is supported. In particular:
Negative dates (dates prior to year 1 BCE), which begin with a hyphen (e.g.
–2018–10–26T21:32:52), are not supported.
Hyphen and colon separators are required: so-called “basic” format,
YYYYMMDDThhmmss, is not supported.
Ordinal dates (year plus day of year, calendar week plus day number) are not supported.
Using more than four digits for the year is not supported.
Supported dates and times include the following:
Unsupported dates and times include the following:
2018-10-26T21:32(if a time is specified then all of its parts must be present)
2018-10-26T25:32:52+02:00(the hours part, 25, is out of range)
18-10-26T21:32(the year is not specified fully)
Oracle Database Syntax for ISO Durations
Oracle Database supports ISO durations, but Simple Oracle Document Access (SODA) does not support them.
There are two supported Oracle Database syntaxes for ISO durations, the
ds_iso_format specified for SQL function
and the ym_iso_format specified for SQL function
to_dsinterval returns an
instance of SQL type
INTERVAL DAY TO SECOND, and
to_yminterval returns an instance of type
These formats are used for data types
respectively, which Oracle has added to the JSON language.
PdDThHmMsS, where d, h, m, and s are digit sequences for the number of days, hours, minutes, and seconds, respectively. For example:
s can also be an integer-part digit sequence followed by a decimal point and a fractional-part digit sequence. For example:
Any sequence whose value would be zero is omitted, along with its designator. For example:
"PT3M3.141593S". However, if all sequences would have zero values then the syntax is
PyYmM, where y is a digit sequence for the number of years and m is a digit sequence for the number of months. For example:
If the number of years or months is zero then it and its designator are omitted. Examples:
"P8M". However, if there are zero years and zero months then the syntax is