Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
NLS_TIMESTAMP_FORMAT in Timesten

Hi there,
Please check the below logs from "Oracle DB" and from "TimesTen DB".
-We are basically trying to insert a timestamp value as a string from a Pro*C program.
-There is no explicit conversion to a specified format in the application as we are setting the NLS_TIMESTAMP_FORMAT in the session as 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT'. It is bad practice, but we have inherited this legacy code.
-While "insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM');" works in Oracle, it fails in Timesten. We are assuming it is because of different NLS_TIMESTAMP_FORMAT in TimesTen database.
-To prove, it is because of different NLS_TIMESTAMP_FORMAT, we can see that "insert into test_timestamp (col1) values ('2020-02-10 12:46:48.802050000');" works in TimesTen.
Query:
-Is there an easier way handle this incompatibility? We would ideally want to not change the app code to reformat the string to match TimesTen NLS_TIMESTAMP_FORMAT.
-What we understand is that we cannot ALTER SESSION in TimesTen to change the NLS_TIMESTAMP_FORMAT. So it is currently forcing to reformat the string in our application in million places to make the piece of code work with TimesTen.
-Is there a way to set NLS_TIMESTAMP_FORMAT globally in the TimesTen?
===============On Oracle================
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options SQL> create table test_timestamp ( col1 timestamp(6) ); Table created. SQL> insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM'); 1 row created. SQL> commit; Commit complete. SQL> select value from nls_database_parameters where parameter = 'NLS_TIMESTAMP_FORMAT'; VALUE ---------------------------------------------------------------- DD-MON-RR HH.MI.SSXFF AM SQL>
$ ttisql "uid=<removed>;pwd=<removed>;dsn=sampledb"Copyright (c) 1996, 2019, Oracle and/or its affiliates. All rights reserved.Type ? or "help" for help, type "exit" to quit ttIsql.connect "uid=<removed>;pwd=********;dsn=<removed>";Connection successful: DSN=<removed>;UID=<removed>;DataStore=<removed>;DatabaseCharacterSet=WE8ISO8859P1;ConnectionCharacterSet=WE8ISO8859P1;DRIVER=<removed>/install/lib/libtten.so;PermSize=40960;TempSize=256;OracleNetServiceName=<removed>;(Default setting AutoCommit=1)Command>Command> create table test_timestamp ( col1 timestamp(6) );Command>Command> insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM'); 2813: Error converting from character string '10-FEB-20 12.46.48.802050 PM' to Oracle timestampThe command failed.Command>Command> insert into test_timestamp (col1) values ('2020-02-10 12:46:48.802050000');1 row inserted.Command>Command> commit;Command>Command><code>===============On TimesTen================<span style="font-size: 8pt;"><br/></span>
Answers
-
One of the bigger issues here is the use of 2 digit years! Surely that warrants editing the App code to fix...
WRT to your posted issue, change the NLS_TIMESTAMP_FORMAT for Oracle inserts to match Timesten TIMESTAMP format
'yyyy-mm-dd hh24:mi:ss.ffffff'
and change the format of the timestamp strings to that format, it will then work on both db's.
For your example, format the timestamp data as
'2020-02-10 12:46:48.802050'