ORA-01839 "date not valid for month specified" for to_date in where clause

Question

I have following query (BOCRTNTIME - varchar e.g 2015-02-28 12:21:45, VIEW_BASE_MARIX_T - some view):

select BOCRTNTIME
    from VIEW_BASE_MARIX_T
    where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD')
        between (to_date ('2016-01-01', 'YYYY-MM-DD'))
            and (to_date ('2016-02-01', 'YYYY-MM-DD'))

On executing I get error:

ORA-01839:  "date not valid for month specified"

I thought that there are can be incorrect data in BOCRTNTIME, so execute following query:

select distinct
         substr(BOCRTNTIME,1,8),
         substr(BOCRTNTIME,9,2)
  from VIEW_BASE_MARIX_T
 order by substr(BOCRTNTIME,9,2);

But everything looks fine: http://pastebin.com/fNjP4UAu. Also following query executes without any error:

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') 
  from VIEW_BASE_MARIX_T;

I already tried add trunc() to all to_date() but no luck. Also I create pl/sql procedure that takes one by one item form VIEW_BASE_MARIX_T and convert it to date - and everything works just fine. Any ideas why I get error on first query?

UPD: Query on table that used in view works fine, but in view - not

UPD2: We have few enviroments with same products, but get error only on one

UPD3: Issue was resolved by search non valid dates in table that used in view

the select of this `select substr(BOCRTNTIME,1,10)` is `"2015-07-"` ? if yes then thats the problem if your error . it should containt a day value to work fine , for example "2015-07-01". what is the value of BOCRTNTIME? — Moudiz, Feb 08 '16 at 12:58
When you tried `select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') from VIEW_BASE_MARIX_T;` did you make sure you selected all rows? Some tools like SQL-Developer will only show e.g. 50 results; the error could appear with a later result. — Erich Kitzmueller, Feb 08 '16 at 13:14
@MT0 there are view: pastebin.com/UntX5FfV and table used in view: pastebin.com/PcFppgAg — Dmitry Kompot, Feb 08 '16 at 13:49

MT0 · Answer 1 · 2016-02-08T13:18:27.970

4

A bit too long for a comment - create a simple function to test the dates:

CREATE FUNCTION is_Valid_Date(
  in_string VARCHAR2,
  in_format VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN NUMBER DETERMINISTIC
AS
  dt DATE;
BEGIN
  dt := TO_DATE( in_string, in_format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

Then you can do:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  is_Valid_Date( substr(BOCRTNTIME,1,10) ) = 0;

You will possibly find that April, June, September or November have an entry for the 31st of that month or February has values greater than 28/29th (although I can't see anything like that in your pasted data).

Otherwise you could try using ANSI Date literals:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between DATE '2016-01-01' and DATE '2016-02-01';

or, even simpler, given the format of the input:

SELECT BOCRTNTIME
FROM   VIEW_BASE_MARIX_T
WHERE  substr(BOCRTNTIME,1,10) between '2016-01-01' and '2016-02-01';

edited Feb 08 '16 at 13:18

answered Feb 08 '16 at 13:12

MT0

86,097
7
42
90

First query (with function) return nothing, Second - ORA-01839, And what about third - I don't think that compare dates as varchar - good idea – Dmitry Kompot Feb 08 '16 at 13:23
You have the dates stored in ISO8601 format (or near enough) - there is no issue with using a string comparison to test if a **valid** date is within a date range using this format. That was one of the reasons for which the format was designed. – MT0 Feb 08 '16 at 13:27
Thanks, using your function and idea from @Ricardo Arnold about running it against table, but not view I find errorneus date records – Dmitry Kompot Feb 08 '16 at 18:18

Ricardo Arnold · Accepted Answer · 2016-02-08T15:08:27.557

1

I think that what might be happening is that Oracle is pushing the predicate to the underlying tables of the view.

Have you tried to run the query

select to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') BOCRTNTIME
from MY_TABLE

instead of querying the view?

you can also confirm this by using the NO_PUSH_PRED hint

select /*+ NO_PUSH_PRED(VIEW_BASE_MARIX_T) */
BOCRTNTIME
from VIEW_BASE_MARIX_T
where 
to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))

edited Feb 08 '16 at 15:08

answered Feb 08 '16 at 13:17

Ricardo Arnold

863
1
11
20

@Moudiz: what do you mean? – Ricardo Arnold Feb 08 '16 at 13:22
Quering on table, not view, works fine, but on view with NO_PUSH_PRED hint still fails – Dmitry Kompot Feb 08 '16 at 13:28
@DmitryKompot can you maybe provide the scripts for building the table and the view and also insert scripts? – Ricardo Arnold Feb 08 '16 at 13:40
I don't have insert scripts, but there are view: http://pastebin.com/UntX5FfV and table used in view: http://pastebin.com/PcFppgAg – Dmitry Kompot Feb 08 '16 at 13:48
What happens when you do the following: `create table my_tab_test as select * from VIEW_BASE_MARIX_T` and afterwards run the same query on the newly created table? `select BOCRTNTIME from MY_TAB_TEST where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))` – Ricardo Arnold Feb 08 '16 at 13:54
@DmitryKompot have you tried with creating a table from the view? – Ricardo Arnold Feb 08 '16 at 14:11
Select from MY_TAB_TEST works fine. Also there is execution plan from sql developer that i get from select from view with NO_PUSH_PRED hint http://pastebin.com/G2mkvzEH – Dmitry Kompot Feb 08 '16 at 14:12
@DmitryKompot Then the problem is with predicate pushing as I suspected. Your execution plan shows that the predicate is pushed to the table ` TABLE ACCESS BODAT_QUALITYCLAIM 12631 188975 Filter Predicates AND TO_DATE(SUBSTR(BODAT_QUALITYCLAIM.BOCRTNTIME,1,10),'YYYY-MM-DD')>=TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') – Ricardo Arnold Feb 08 '16 at 14:23
@DmitryKompot can you maybe review how you use the NO_PUSH_PRED? and can you also run the TO_DATE query against BODAT_QUALITYCLAIM – Ricardo Arnold Feb 08 '16 at 14:24
I just exec query provided by you `select /*+ NO_PUSH_PRED (VIEW_BASE_MARIX_T) */ BOCRTNTIME from VIEW_BASE_MARIX_T where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))`. TO_DATE query against BODAT_QUALITYCLAIM work just fine – Dmitry Kompot Feb 08 '16 at 14:30
And what wrong with predicate pushed to the table? From my point of view everything fine. Also is there any way to not use hints? – Dmitry Kompot Feb 08 '16 at 14:32
Hi @DmitryKompot, The problem is that there is lots of data in the table that might not end up in the view. The where conditions in the view are filtering the data and therefore you don't get the problem while querying the view just with TO_DATE as part of the SELECT. With predicate_pushing, oracle is forcing this condition to be evaluated agains a bigger set of data (perhaps all data in your table) , where you probably have invalid dates, casing the whole query to fail. – Ricardo Arnold Feb 08 '16 at 14:53
The idea of predicate pushing is to improve performance by evaluating filtering crytheria in the deepest level of the view therefore reducing the amount of data to process and also taking advantage of indexes wherever possible – Ricardo Arnold Feb 08 '16 at 14:56
Hi @DmitryKompot Can you maybe try again using an alias for the view? `select /*+ NO_PUSH_PRED(V) */ V.BOCRTNTIME from VIEW_BASE_MARIX_T V where to_date(substr(V.BOCRTNTIME,1,10),'YYYY-MM-DD') between (to_date ('2016-01-01', 'YYYY-MM-DD')) and (to_date ('2016-02-01', 'YYYY-MM-DD'))` – Ricardo Arnold Feb 08 '16 at 15:05
@DmitryKompot Please try again with the query above, I think that the problem was not the alias but the blank space between NO_PUSH_PRED and the Brackets – Ricardo Arnold Feb 08 '16 at 15:08
Thanks, I find incorrect dates in BODAT_QUALITYCLAIM. – Dmitry Kompot Feb 08 '16 at 18:15

Emil Moise · Answer 3 · 2016-02-08T14:27:11.013

0

It might be a long shot but between doesn't have parenthesis in it's syntax, have you tried removing them?

select
    BOCRTNTIME
    from VIEW_BASE_MARIX_T
    where 
    to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') between to_date ('2016-01-01', 'YYYY-MM-DD') and to_date ('2016-02-01', 'YYYY-MM-DD')

edited Feb 08 '16 at 14:27

answered Feb 08 '16 at 13:05

Emil Moise

373
1
7

1

I'll find it very surprising if this is the cause to the error. – sagi Feb 08 '16 at 13:07
No, even `where to_date(substr(BOCRTNTIME,1,10),'YYYY-MM-DD') = to_date ('2015-06-01', 'YYYY-MM-DD')` not work – Dmitry Kompot Feb 08 '16 at 13:08
Well, it was worth trying in the case oracle was only considering the entire parenthesis as an argument for the lower limit in the between clause.. – Emil Moise Feb 08 '16 at 13:10

score 0 · Answer 4 · answered Feb 08 '16 at 13:18

The error message says that you have an invalid day in your month. Check your data with this:

-- Check for months with 30 days
select substr(BOCRTNTIME,9,2), substr(BOCRTNTIME,6,2) from VIEW_BASE_MARIX_T where to_number(substr(BOCRTNTIME,6,2)) in (4,6,9,11) and to_number(substr(BOCRTNTIME,9,2))>30;

-- Check for february
select substr(BOCRTNTIME,9,2), substr(BOCRTNTIME,6,2) from VIEW_BASE_MARIX_T where to_number(substr(BOCRTNTIME,6,2))=2 and to_number(substr(BOCRTNTIME,9,2))>28;

As you can see from my pastebin link there is no such items – Dmitry Kompot Feb 08 '16 at 13:25 — Dmitry Kompot, Feb 08 '16 at 13:25

ORA-01839 "date not valid for month specified" for to_date in where clause

4 Answers4

Linked