How to convert a JSON date to an Oracle date in local time

When you use to_timestamp_tz() but don't actually specify the time zone in the conversion it defaults to your system time zone, which presumably isn't UTC. The timestamp with time zone you are generating is therefore already in your local system time zone, so at local isn't doing anything.

You can convert to a plain timestamp instead, and declare the time zone as UTC with the from_tz() function; then still use the AT LOCAL expression to change it to your local time zone:

alter session set time_zone = 'Europe/Vienna';SELECT CAST(  FROM_TZ(TO_TIMESTAMP('2016-11-29T23:00:00.000Z', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"'),    'UTC') AT LOCAL AS DATE) FROM DUAL;CAST(FROM_TZ(TO_TIM-------------------2016-11-30 00:00:00

Breaking that down a bit:

1. TO_TIMESTAMP('2016-11-29T23:00:00.000Z', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') converts your string value to a plain TIMESTAMP value, with no time zone information.
2. FROM_TZ(..., 'UTC') converts that plain timestamp to a time stamp with time zone with the time zone part as UTC - no adjustment is done to any of the date/time elements, it just states those represent a UTC value.
3. ... AT LOCAL converts to your session time zone (which might not be the same as your system time zone).
4. CAST(... AS DATE) converts the value in your local time zone to a date; again no adjustment is done to the element values, but you lose the fractional seconds and time zone information.

You could also stick with to_timestamp_tz() but include the UTC code:

SELECT CAST(  TO_TIMESTAMP_TZ('2016-11-29T23:00:00.000Z' || 'UTC',    'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"TZR')  AT LOCAL AS DATE) FROM DUAL;

or replace the Z with UTC:

SELECT CAST(  TO_TIMESTAMP_TZ(REPLACE('2016-11-29T23:00:00.000Z', 'Z', 'UTC'),    'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3TZR')  AT LOCAL AS DATE) FROM DUAL;

All of these assume - correctly, I believe - that the JSON string will always be UTC and the Z can be assumed to be there and to mean that (as it should do, of course).