The RETURNING DATE clause for SQL functions JSON_VALUE and JSON_TABLE returns a DATE value with the time component zeroed out (truncated) in releases prior to Oracle Database 21c. The analogous DATE () item method in the SQL/JSON path language has the same behavior.

Workaround:

In release 21c, this continues to be the default behavior for the RETURNING DATE clause. However, there is a new, optional behavior that preserves the time component of a DATE value for consistency with the Oracle DATE data type. This new behavior is expected to become the default in a future release.

The new behavior is now available using the RETURNING DATE PRESERVE TIME clause or using a new item method, dateWithTime (). Time truncation, although the default in release 21c, can also be specified explicitly using the RETURNING DATE TRUNCATE TIME clause.

To minimize the impact to applications from the expected change in default behavior, Oracle Corporation recommends that, at least in new code, you explicitly use the PRESERVE TIME or TRUNCATE TIME clause, as appropriate, and use the item method dateWithTime () where appropriate.

For most use cases, the PRESERVE TIME clause and the item method dateWithTime () are preferable. However, using the TRUNCATE TIME clause continues to ensure the same behavior that is the default in release 21c.

For an index to be used, the time handling (truncate or preserve) of the query and the index must match. An application time-handling change requires rebuilding any persistent objects that depend on it, such as indexes, materialized views, partitions, check constraints, and PL/SQL subprograms.