Declare the column as a timestamp:
with rws as (
select '{
"dates" : [
"2019-07-15",
"15-JUL-2019",
"2019-07-15 02:11:27 PM",
"2019-07-15 14:11:27",
"2019-07-15T14:11:27",
"2019-07-15T14:11:27Z",
"2019-07-15T14:11:27+00:00"
]
}' doc
from dual
)
select j.*
from rws, json_table (
doc, '$'
columns
nested path '$.dates[*]'
columns (
dt_str varchar2 path '$',
dt_dt timestamp path '$' null on error
)
) j;
DT_STR DT_DT
2019-07-15 15-JUL-2019 00.00.00.000000000
15-JUL-2019
2019-07-15 02:11:27 PM
2019-07-15 14:11:27
2019-07-15T14:11:27 15-JUL-2019 14.11.27.000000000
2019-07-15T14:11:27Z 15-JUL-2019 14.11.27.000000000
2019-07-15T14:11:27+00:00 15-JUL-2019 14.11.27.000000000