Re: ORA-01843: not a valid month

Sorry it was my mistake to forgot to mention that cdatetime is TIMESTAMP columnSQL> select  cdatetime from ptran.qtransaction  where rownum < 5;

PXCREATEDATETIME---------------------------------------------------------------------------19-SEP-22 09.59.48.503000 AM19-SEP-22 09.59.48.645000 AM19-SEP-22 10.06.49.518000 AM19-SEP-22 10.06.49.597000 AM
SQL> _at_desc ptran.qtransaction            Name                            Null?    Type           ------------------------------- -------- ----------------------------    1      CXKEY                                    VARCHAR2(255 CHAR)    2      CXCOUNT                            NUMBER(18)    3      CXAMOUNT                        NUMBER(18)    4      CXSYSTEMID                      NUMBER(18)    5      CDATETIME                                TIMESTAMP(6)    6      CXPNAME                                  VARCHAR2(128 CHAR)

    On Tuesday, January 17, 2023 at 04:28:01 PM EST, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:  

 Since you haven't sent the full DDL from both environments, will take an assumption, that what you write is correct and cdatetime column is of type TIMESTAMP...Then constant '2023-01-12 01:01:01.122' should be converted to TIMESTAMP according to (SELECT value FROM nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT') - from the trace file it shows the value DD-MON-RR HH.MI.SSXFF AM. Which is not how the constant is formatted, so the error message is correct. SQL> alter session set nls_timestamp_format='DD-MON-RR HH.MI.SSXFF AM';

Session altered.

SQL> select * from dual where sys_extract_utc(systimestamp) < '2023-01-12 01:01:01.122';

Error starting at line : 98 in command - select * from dual where sys_extract_utc(systimestamp) < '2023-01-12 01:01:01.122' Error report -
ORA-01843: not a valid month

And bug should be in the Dev environment. Could it be that cdatetime has a different datatype in Dev?It almost matches NLS_DATE_FORMAT, but it is missing the fractional seconds and you should get - ORA-01830: date format picture ends before converting entire input string SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from dual where sysdate < '2023-01-12 01:01:01.122';

Error starting at line : 92 in command - select * from dual where sysdate < '2023-01-12 01:01:01.122' Error report -
ORA-01830: date format picture ends before converting entire input string

So... could it be that cdatetime is created as VARCHAR in dev? Wouldn't be the first time seeing that - Oracle even recommends it in Doc ID 1553906.1 (intended as a joke here, although the note exists and the rare use case is real). On Tue, Jan 17, 2023 at 9:18 PM Sanjay Mishra <smishra_97_at_yahoo.com> wrote:

 Ilmar

I am running the Test Query from the same client remotely using Sqlplus or even Toad. Sanjay

    On Tuesday, January 17, 2023 at 01:32:19 PM EST, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:  

 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 databaseDatabase Parameter are same in bothNLS_TIMESTAMP_TZ_FORMAT                     DD-MON-RR HH.MI.SSXFF AM TZRNLS_TIME_TZ_FORMAT                          HH.MI.SSXFF AM TZRNLS_TIMESTAMP_FORMAT                        DD-MON-RR HH.MI.SSXFF AMNLS_TIME_FORMAT                             HH.MI.SSXFF AM show parameter nlsSQL> show parameter nls

PARAMETER_NAME                                               TYPE        VALUE------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------nls_calendar                                                 string      GREGORIANnls_comp                                                     string      BINARYnls_currency                                                 string      $nls_date_format                                              string      YYYY-MM-DD HH24:MI:SSnls_date_language                                            string      AMERICANnls_dual_currency                                            string      $nls_iso_currency                                             string      AMERICAnls_language                                                 string      AMERICANnls_length_semantics                                         string      CHARnls_nchar_conv_excp                                          string      FALSEnls_numeric_characters                                       string      .,nls_sort                                                     string      BINARYnls_territory                                                string      AMERICAnls_time_format                                              string      HH.MI.SSXFF AMnls_time_tz_format                                           string      HH.MI.SSXFF AM TZRnls_timestamp_format                                         string      DD-MON-RR HH.MI.SSXFF AMnls_timestamp_tz_format                                      string      DD-MON-RR HH.MI.SSXFF AM TZR

TIASanjay

--

Ilmar Kerm

--

Ilmar Kerm

--

Ilmar Kerm

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