RE: ORA-01843: not a valid month

Was this solved? The oracle-base article is excellent, but it is a tad frustrating that error report is ‘invalid month’ without puking out the format string and the string value of the timestamp value.  

That is sort of tragic since both those values must be in hand AND they could be puked out without even increasing the vertical space.  

Sigh. MON versus MM seems like like a common visual error to miss, sort of like typing the same word twice in a row.  

Good luck, and so sorry about thinking my comment about fractional seconds would be funny.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra ("smishra_97") Sent: Tuesday, January 17, 2023 5:00 PM
To: Oracle-L Freelists; Ilmar Kerm
Subject: Re: ORA-01843: not a valid month    

Sorry it was my mistake to forgot to mention that cdatetime is TIMESTAMP column

SQL> select cdatetime from ptran.qtransaction where rownum < 5;  

PXCREATEDATETIME

           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)

 

 

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 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  

--

Ilmar Kerm

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 18 2023 - 21:34:46 CET