How do I fix ORA-01843: not a valid month?

So at the query level, I have it:

to_char(
    (
        to_date(
        substr(TIMESTAMP, 1, 19),
        'yyyy-mm-dd hh24:mi:ss'
        )
    ),
    'dd-mon-yyyy hh24:mi:ss'
    ) as DateTime,

And I’ve tried looking at a few articles with one most notable:

How to change the date format in Oracle BI Publisher?

I have also tried using:

and trunc(TIMESTAMP) between :FROM_DATE AND :TO_DATE
--and also
and trunc(TIMESTAMP) between to_date(:FROM_DATE, 'yyyy-MM-dd') AND to_date(:TO_DATE, 'yyyy-MM-dd')

While going through structure and XML I noticed my date is in string format:

element name=”DATETIME” value=”DATETIME” label=”DATETIME” dataType=”xsd:string” breakOrder=”ascending” fieldOrder=”3″

So I removed the to_char to get the date format

The error I’ve been getting is:

java.sql.SQLDataException: ORA-01843: not a valid month

How do I fix this issue?

EDIT: Format for the column, TIMESTAMP, the format is CHAR(14) Example of values is like 20200701103038 It runs perfectly in SQL Developer

Answer

Well, it is quite a bad and extended practice to store DATES as strings, either using varchar2 or char. Anyway, having say that, I think you have a problem with your settings or the way you are constructing your query:

SQL> alter session set nls_date_format='YYYYMMDDHH24MISS' ;

Session altered.

SQL> select to_date('20200726123722') from dual ;

TO_DATE('20200
--------------
20200726123722

SQL> select sysdate from dual ;

SYSDATE
--------------
20200726124622

Besides, as you said, if your data is stored as YYYYMMDDHHMISS, you are applying the wrong date mask YYYY-MM-DD HH24:MI:SS to that char. I would use CAST to define the field as DATE.

Example

SQL> create table my_test ( c1 char(20) ) ;

Table created.

SQL> insert into my_test values ('20200726123722') ;

1 row created.

SQL>  insert into my_test values ('20200725123722') ;

1 row created.

SQL> commit ;

Commit complete.

SQL>  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select cast(c1 as date) from my_test ;

CAST(C1ASDATE)
-------------------
2020-07-26 12:37:22
2020-07-25 12:37:22

SQL>

Update

If you can’t change your NLS Session settings, then you must apply a TO_CHAR to the resulting output. But in your case, you want to operate with dates, so as long as it is a date value you want to operate with, you can forget about the mask.

SQL> col value for a20
SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT' ;

VALUE
--------------------
DD-MON-RR

SQL> select cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date) from dual ;

CAST(TO_D
---------
25-JUL-20

SQL> select to_char( cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date) , 'YYYYMMDDHHMISS' ) from dual ;

TO_CHAR(CAST(T
--------------
20200725123722

SQL> select case when cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date)  > sysdate
  2  then 'FALSE'
  3  else
  4  'TRUE'
  5  end as result from dual ;

RESUL
-----
TRUE

SQL>

So, if you want to compare the date to another date, don’t use to_char. If you want to show the value in a specific format, when you have no option to change the settings, then use to_char.