By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table FUNDING_REQUEST (REQUEST_CONTENTS VARCHAR2(4000));
insert into FUNDING_REQUEST (REQUEST_CONTENTS)
values ('{
"leasing_information": {
"consumer_request_date_time": "2021-09-25T12:30:00.000+02:00"
}
}');
1 rows affected
-- original working query, with just valid values
SELECT *
FROM FUNDING_REQUEST f
ORDER BY TO_TIMESTAMP_TZ(JSON_VALUE(
f.REQUEST_CONTENTS,
'$.leasing_information.consumer_request_date_time'
), 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM') ASC
| REQUEST_CONTENTS |
|---|
| { "leasing_information": { "consumer_request_date_time": "2021-09-25T12:30:00.000+02:00" } } |
insert into FUNDING_REQUEST (REQUEST_CONTENTS)
values ('{
"leasing_information": {
"consumer_request_date_time": "2021-25-09T12:30:00.000+02:00"
}
}');
1 rows affected
-- original working query, with invalid value
SELECT *
FROM FUNDING_REQUEST f
ORDER BY TO_TIMESTAMP_TZ(JSON_VALUE(
f.REQUEST_CONTENTS,
'$.leasing_information.consumer_request_date_time'
), 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM') ASC
ORA-01843: not a valid month
-- original non-working query
SELECT *
FROM FUNDING_REQUEST f
ORDER BY TO_TIMESTAMP_TZ(JSON_VALUE(
f.REQUEST_CONTENTS,
'$.leasing_information.consumer_request_date_time'
) DEFAULT NULL ON CONVERSION ERROR, 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM') ASC
ORA-00932: inconsistent datatypes: expected - got TIMESTAMP WITH TIME ZONE
SELECT *
FROM FUNDING_REQUEST f
ORDER BY JSON_VALUE(
f.REQUEST_CONTENTS,
'$.leasing_information.consumer_request_date_time'
RETURNING TIMESTAMP WITH TIME ZONE
NULL ON ERROR
) ASC
| REQUEST_CONTENTS |
|---|
| { "leasing_information": { "consumer_request_date_time": "2021-09-25T12:30:00.000+02:00" } } |
| { "leasing_information": { "consumer_request_date_time": "2021-25-09T12:30:00.000+02:00" } } |
SELECT JSON_VALUE(
f.REQUEST_CONTENTS FORMAT JSON,
'$.leasing_information.consumer_request_date_time'
) as str,
JSON_VALUE(
f.REQUEST_CONTENTS FORMAT JSON,
'$.leasing_information.consumer_request_date_time'
RETURNING TIMESTAMP WITH TIME ZONE
NULL ON ERROR
) as tstz
FROM FUNDING_REQUEST f
| STR | TSTZ |
|---|---|
| 2021-09-25T12:30:00.000+02:00 | 25-SEP-21 12.30.00.000000 +02:00 |
| 2021-25-09T12:30:00.000+02:00 | null |