Document Type | Troubleshooting
Category | Administration
Document Number | TADTS063
Issue
An error occurs when changing the time of a set region independently from the server without changing the server time in the DB.
When executing the query below, Tibero : TBR-5104: Invalid timezone region value error occurs.
select timestamp '2000-10-28 11:24:54 PM America/New_York' from dual;
Also, when creating a table and inserting data as below, BR-5042: Invalid decimal input. error is observed.
create table t99277
(
col1 timestamp(0),
col2 timestamp(0) with time zone
) ;
insert into t99277 values ('2000-10-28 11:24:54 PM', '2000-10-28 11:24:54 PM America/New_York');
Cause
The ANSI literal form of timezone timestamp has a fixed format, so the format settings specified by the ALTER command are not applied, causing the error.
Solutions
After modifying NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT and then executing, you can confirm that one row is inserted successfully.
alter session set NLS_DATE_LANGUAGE=AMERICAN; alter session set NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH:MI:SS AM"; alter session set NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH:MI:SS AM TZR";
Example of Successful Insertion Confirmation
SQL> select *from t99277; COL1 ----------------------------------------------------------------- COL2 -------------------------------------------------------------------------------- 2000-10-28 11:24:54 PM 2000-10-28 11:24:54 PM America/New_York 1 row selected.