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

Question

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

and what is the format of the data inside the column ? yyyy-mm-dd hh24:mi:ss ?? I need to make a reproducible case — Roberto Hernandez, Jul 26 '20 at 10:33
so for me to understand, you have dates in your char column like this : 20200726123722 — Roberto Hernandez, Jul 26 '20 at 10:38
Clearly the data is in `YYYYMMDDHH24MISS` format but you are getting away with `yyyy-mm-dd hh24:mi:ss` because Oracle is quite forgiving about delimiters in date formats. I would still use the actual format though, to be safe. However I'm not sure what you mean by *So I removed the to_char to get the date format*. Are you saying something doesn't work in Oracle BI Publisher? Where do Java and XML fit in? — William Robertson, Jul 26 '20 at 14:49
Not sure this is feasible but I'd try to fix (or get fixed) the underlying issue which is that the datatype of this column is incorrect. — Koen Lostrie, Jul 26 '20 at 15:24
@WilliamRobertson i was troubleshooting since db column is var format. — Thabiso Motswagole, Jul 26 '20 at 15:44
@KoenLostrie yeah I'm starting to realize that. Dealing with other dev mistakes is a pain — Thabiso Motswagole, Jul 26 '20 at 15:45

score 3 · Answer 1 · answered Jul 26 '20 at 10:34

3

Just to make sure what SYSDATE (I'm going to select) represents:

SQL> alter session set nls_Date_format = 'dd.mm.yyyy';

Session altered.

Today is:

SQL> select sysdate from dual;

SYSDATE
----------
26.07.2020

This is the way to get the error you got: apply wrong format mask to a string which represents a DATE value:

SQL> select to_Date('2020-27-07', 'yyyy-mm-dd') from dual;
select to_Date('2020-27-07', 'yyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

How to fix it? Usually, it is hard to fix it if dates are represented as strings. They (strings that represent dates) are like a box of chocolates, you never know what you're gonna get. If there's at least one wrong value, query will fail.

How to find wrong values? You could create a function which returns TRUE (or 1 or whatever you want) if a string you pass to it represents a valid date format. But, if you pass 01/02/03, which is which? Different formats match (e.g. dd/mm/yy, yy/mm/dd, mm/yy/dd ...). Worse cases are 84/25/32 or AB/23/2f. They are all strings, they "match" two characters separated by slash but certainly aren't valid dates, so you can't rely on a simple regular expression.

Shortly, there's no easy nor fast way out of it.

answered Jul 26 '20 at 10:34

Littlefoot

131,892
15
35
57

In SQL developer this query runs properly... though – Thabiso Motswagole Jul 26 '20 at 10:47
SQL Developer doesn't return all rows that exist, but (depending on settings) 50, 100 or maybe 500 of them. Navigate to the end of the whole record set and see what happens. – Littlefoot Jul 26 '20 at 10:50
I just did and all values are ok – Thabiso Motswagole Jul 26 '20 at 10:52
Good, then you don't have a problem. – Littlefoot Jul 26 '20 at 10:53
This table is made from timestamp generated in java and sent to db. It is also validated before input – Thabiso Motswagole Jul 26 '20 at 10:53
OK, so did you apply correct format mask to those values? If "dates" look like 26.07.2020, you can't apply MM-DD-YYYY to it but DD.MM.YYYY. Did you check that? – Littlefoot Jul 26 '20 at 10:57
I'll check that – Thabiso Motswagole Jul 26 '20 at 10:58
1

In Oracle 12.2 and newer you don't have to create a function, you can use the `DEFAULT ... ON CONVERSION ERROR` clause in `TO_DATE` function. – Wernfried Domscheit Jul 26 '20 at 13:53

Roberto Hernandez · Accepted Answer · 2020-07-26T16:29:11.227

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.

yeah it is bad practice but unfortunately, I am not the dba or original developer. I will try this though — Thabiso Motswagole, Jul 26 '20 at 11:03
ok, let me know in the comments if you have a problem with this — Roberto Hernandez, Jul 26 '20 at 11:05
@ThabisoMotswagole, don't forget to upvote the answer if this help you — Roberto Hernandez, Jul 26 '20 at 13:13
Just the first and last paragraph of your answer is what most so-called "DBAs" should carve in their souls with fire. But as the OP said, unfortunately, it doesn't always depend on us but the original developer. Take a beer you two, drink with me. Cheers. — Metafaniel, Aug 24 '22 at 03:14

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

2 Answers2