Re: ORA-01843: not a valid month

Mark, one of the headline updates in 23c is better error messages. I can’t find where I saw the specifics, maybe someone else will remember, but this scenario is one that I think gets covered.

Thanks,
Andy

On Wed, Jan 18, 2023 at 12:36 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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
>
> ---------------------------------------------------------------------------
>
> 19-SEP-22 09.59.48.503000 AM
>
> 19-SEP-22 09.59.48.645000 AM
>
> 19-SEP-22 10.06.49.518000 AM
>
> 19-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 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