NLS session parameters are set by the client - check client OS environment,
java regional settings, ....
On Tue, Jan 17, 2023 at 6:46 PM Sanjay Mishra <smishra_97@xxxxxxxxx> wrote:
Thanks Ilmar for the details. I am trying to check what is different in
the environment as Code/SQL is generated using 3rd Party application and so
cannot change, It is working in a lower environment but failed in Perf
testing. I can see that using timestamp or nls_timestamp_format, it is
working but as code cannot be changed and so was trying to see what is
impacting it
Sanjay
On Tuesday, January 17, 2023 at 12:22:02 PM EST, Ilmar Kerm <
ilmar.kerm@xxxxxxxxx> wrote:
You pass the timestamp value as a string not as a timestamp, so it is
subject to SESSION nls timestamp formatting rules.
Never rely on "magic" implicit datatype conversions - you'll be subject to
inconsistent behaviour like this.
Instead of '2023-01-12 01:01:01.122' you can write timestamp'2023-01-12
01:01:01' or use to_timestamp() function.
https://oracle-base.com/blog/2020/07/08/when-implicit-date-conversions-attack/
"Relying on an implicit conversion is *ALWAYS* a bug waiting to happen."
On Tue, Jan 17, 2023 at 6:03 PM Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hi
I had Dev and Perf setup where one of the Application Column is TIMESTAMP
and the query is giving an error in Perf but working in Dev
select cname,cvalue from ptran.qtransaction where cdatetime <=
'2023-01-12 01:01:01.122'
ERROR at line 1:
ORA-01843: not a valid month
It is working fine in other database
Database Parameter are same in both
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
show parameter nls
SQL> show parameter nls
PARAMETER_NAME TYPE
VALUE
------------------------------------------------------------ -----------
----------------------------------------------------------------------------------------------------
nls_calendar string
GREGORIAN
nls_comp string
BINARY
nls_currency string $
nls_date_format string
YYYY-MM-DD HH24:MI:SS
nls_date_language string
AMERICAN
nls_dual_currency string $
nls_iso_currency string
AMERICA
nls_language string
AMERICAN
nls_length_semantics string
CHAR
nls_nchar_conv_excp string
FALSE
nls_numeric_characters string .,
nls_sort string
BINARY
nls_territory string
AMERICA
nls_time_format string
HH.MI.SSXFF AM
nls_time_tz_format string
HH.MI.SSXFF AM TZR
nls_timestamp_format string
DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string
DD-MON-RR HH.MI.SSXFF AM TZR
TIA
Sanjay
--
Ilmar Kerm
