NLS_TIMESTAMP_FORMAT(NLS_DATE_FORMAT) does not allow Z token
Details
| Detail name | Value |
|---|---|
| Changelog Number | 11182 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0 |
| Fix Versions | Exasol 7.0.5 |
| Resolution Date | 2020-12-18 |
Description
Casting a string to a date or timestamp can be done by defining the format explicitly or implicitly via NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT
Defining the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT via alter session/alter system does not support the z-format
e.g.
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DDTHH24:MI:SS.FF3Z';
this returns the following error
data exception - Unknown timestamp format
Workaround
You can define the format explicitly avoiding NLS_TIMESTAMP_FORMAT, NLS_DATE_FORMAT e.g.
select to_timestamp('1963-06-09T00:00:00.000Z' , 'YYYY-MM-DDTHH24:MI:SS.FF3Z');
Fix
Z-token is allowed in any cases
