Re: ORA-01843: not a valid month

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_at_yahoo.com> 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_at_gmail.com> 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_at_freelists.org>
> 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
>

-- 
Ilmar Kerm

--
http://www.freelists.org/webpage/oracle-l