"not a valid month" even though other datetype operations work

Question

So, I have seen some similar questions asked here but none of the solutions have worked for me. In a view, I generate a datetype field by doing:

TO_DATE(DataOraModifica, 'YYYYMMDDhh24miss') 

I'm querying this view and I want to find all entries more recent than 15 days. but when I try this:

select  dataoramodifica, sysdate-dataoramodifica as dife from myview 
where sysdate-dataoramodifica<15

but I get this error: ORA-01843: not a valid month 01843. 00000 - "not a valid month"

I have read on other posts that it can be related to to_char and to_date conversions as well as the session time format. But I'm not using any to_char, just one to_date directly on my original string

Actually, if I try other operations, such as:

select  dataoramodifica, sysdate-dataoramodifica as dife from myview
where rownum<10

I do get a result that makes sense:

13-AUG-16 1426.998530092592592592592592592592592593

the only problem comes when I try to use this operations for a WHERE condition. I've tried doing to_char and to_date to convert everyithing to the same format and back again, with no result. I would like to avoid altering the session date format since for my view I use some stored procedures related to date (and which I'm not allowed to modify) and this is a working system so I wouldn't want to cause trouble.

Thank you

----Edit----

I don't seem to be handling all the cases in which TO_DATE may fail when using:

length(F_Convert2NormalDate(dataoramodifica))=14 then 
TO_DATE(F_Convert2NormalDate(DataOraModifica), 'YYYYMMDDhh24miss') 
else TO_DATE('19000101000000', 'YYYYMMDDhh24miss') end
AS DATAORAMODIFICA

To see which possible values I have I did:

  select distinct 
substr(f_convert2normaldate(dataoramodifica), 5,2) as mms, from mytable

and the values I got were:

(null) 00 01 02 03 04 05 06 07 08 09 10 11 12

I guess I need to find a way to handle all possible conversion errors cause there are clearly some that I'm missing. The default option won't do cause I'm running version 11...

----Edit---

As most of you suggested, there was a case where conversion was failing even after my 14-character check ('19000000000000'). After adding this to my ´case...then´ it works. It still feels like a hardcoded solution though, but I guess for now that'll have to do since the version I'm running doesn't allow for generic exception handling (such as ´default... on conversion error´ ) and I think the problem must be handled at the data-generation stage by those in charge of it.

Thanks everyone for your help!

Answer 1

the stored procedure ... takes a string produced by the system that is encoded somehow, and returns another string in the way YYYYMMDDhhmmss, if the input does not produce a valid date it returns the string "00".

If it actually returns '00000000000000' (or, as it turns out, '19000000000000') then those will cause that error:

select to_date('00000000000000', 'YYYYMMDDhh24miss') from dual;

ORA-01843: not a valid month

select to_date('19000000000000', 'YYYYMMDDhh24miss') from dual;

ORA-01843: not a valid month

When you query the view without the condition you are either limiting to 10 indeterminate rows with where rownum<10 as you showed, or your client is only fetching and displaying the 'first' few rows or blocks of rows (e.g. 50 rows by default in SQL Developer). The procedure (which is actually a function, presumably...) is only called for those few rows, which happen to not contain any problematic data.

When you have the condition every row has to be evaluated, so you are hitting a problematic one.

You can avoid the issue by changing the view to do something like:

TO_DATE(
  case
    when DataOraModifica = '00' then null
    when DataOraModifica = '00000000000000' then null
    when DataOraModifica = '19000000000000' then null
    else DataOraModifica
  end, 
  'YYYYMMDDhh24miss'
)

db<>fiddle

Incidentally, I'd normally write the condition as

where dataoramodifica >= trunc(sysdate) - 15;

which has to do less work and would allow an index on the column to be used; which isn't likely to matter here as the view column is a function call anyway. Using trunc(sysdate) will include all data from that start date, rather than just from the current time on that date - it's unclear which you actually want.

---

Looking at the length check you were already doing, you could incorporate that as:

case when length(F_Convert2NormalDate(DataOraModifica)) != 14
       or F_Convert2NormalDate(DataOraModifica) = '00'
       or F_Convert2NormalDate(DataOraModifica) = '00000000000000'
       or F_Convert2NormalDate(DataOraModifica) = '19000000000000'
     then date '1900-01-01'
     else TO_DATE(DataOraModifica, 'YYYYMMDDhh24miss')
end

Or if you're on a recent version of Oracle (12.2+) you can let to_date() handle any error condition:

TO_DATE(DataOraModifica default '19000101000000' on conversion error,
  'YYYYMMDDhh24miss')

It's possible that will hide other issues you would want to actually throw an error, so you know there is somethign you need to fix, but it doesn't sound like it from your description.

db<>fiddle

---

To identify values which are causing a problem, you can do something like this:

set serveroutput on
declare
  str varchar2(30);
  dt date;
begin
  for r in (select dataoramodifica from mytable) loop
    begin
      str := F_Convert2NormalDate(r.DataOraModifica);
      dt := case when length(str) = 14
                 then TO_DATE (str, 'YYYYMMDDhh24miss')
                 else TO_DATE('19000101000000', 'YYYYMMDDhh24miss')
            end;
    exception
      when others then
        dbms_output.put_line(r.DataOraModifica || ' -> ' || str || ' => ' || sqlerrm);
    end;
  end loop;
end;
/

That will try to convert every value in the table one by one; when it finds a problem it will report it but carry on. You can add other useful data in the debug of course, like the row's primary key value.

Answer 2

I believe the problem is in your data from your table. You say there is a column with string values and you convert that string to date and then you use this converted column in select and wheer clause.

Here is a example of your possible situation:

If theer is a good string in your table like '20200405222222' then your query will work:

select  to_date('20200405222222', 'YYYYMMDDhh24miss')
        , sysdate-to_date('20200405222222', 'YYYYMMDDhh24miss') as dife 
from dual;

If the string is like '20201305222222' then your query will not work:

select  to_date('20201305222222', 'YYYYMMDDhh24miss')
        , sysdate-to_date('20201305222222', 'YYYYMMDDhh24miss') as dife 
from dual;

Here is a small demo

You can see from my example that Ihave used 13 as a numeric string to represent the month and that is not a valid month...

Here is an even better example where I have created a view and used two different query's:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1f70f43aef1297044261ca813b8022bc