ORA-01843: not a valid month When where condition is changed

Question

I am having problem with the following error

ORA-01843: not a valid month

The following query works well if I use

SELECT  mx.work_order_no, mx.work_order_name, mx.comments
  FROM  max_orders mx
 WHERE  TO_DATE (wo_dt, 'dd/mm/rr') <= (TO_DATE (SYSDATE, 'dd/mon/rr') - 7)

However if I change where condition clause to

WHERE   TO_DATE (wo_dt, 'dd/mm/rr') >= (TO_DATE (SYSDATE, 'dd/mon/rr') - 7)

I am having issue with

ORA-01843: not a valid month

What has caused this and how can I resolve this error?

Update 1

Underlying view

SELECT    work_order_no,
          work_order_name,
                 wo_dt,
                comments
      FROM    (SELECT     mx_master.work_order_no,
                             mx_master.work_order_name,
                             SUBSTR (mx_master.uom, 1, 15) wo_dt,
                             mx_master.remarks
                    FROM     mx_wo_data mx_master)

Maybe you get a value that is not a valid date because of the condition `>` — Jens, Aug 19 '14 at 11:09
The condition is applied after the date conversion though. It would be interesting to see the execution plan to see if the condition change is still altering it; but I suspect there might be other filters that haven't been shown. Either way, `wt_dt` is a string that has values that don't match your pattern; or it's a date and your NLS settings don't match the format model you're using. Probably something wrong with the data, yes - which is why you shouldn't store dates as strings. — Alex Poole, Aug 19 '14 at 11:12
`TO_DATE (SYSDATE, 'dd/mon/rr')` does not make ***any*** sense. `to_date()` converts a `varchar` into a `date`. `sysdate` already **is** a date. To the call first converts `sysdate` to a `varchar` and then converts it back to a `date`. — , Aug 19 '14 at 11:23
Can you add the DDL for the table, and the execution plans for both queries? And can you clarify if the first query works exactly as shown - that you removed the other conditions from both queries for testing? Well, I say table... is `max_orders` a table, or a view? If it is a view please show its definition, and the structure of the underlying tables. Still sounds like an NLS issue.... — Alex Poole, Aug 19 '14 at 11:25
So based on the view definition, `wo_dt` is a string, not a date, and you have values in there which are not representing valid dates for the format model you're using. — Alex Poole, Aug 19 '14 at 11:33
@AlexPoole Yes that is true. Are there any possibility of resolving this issue if this is data corruption? — Jacob, Aug 19 '14 at 11:35

score 3 · Answer 1 · edited May 23 '17 at 11:44

3

SYSDATE is already a date. You should not be passing it into TO_DATE(). When you do that you're doing an implicit conversion to a string, and an explicit conversion back. Gordon Linoff already showed a better way to do that.

Based on the view definition you added, wo_dt is a string. You're expecting that to be in dd/mm/rr format. The error is telling you what you have values in that column which are not actually in that format, so you'll need to examine the data in the view or the underlying table to see which record(s) have incorrect data.

You could use something like this to either exclude the values that are not in the right format; or more usefully identify the bad values so they can be removed or corrected, e.g. with something like:

select * from max_orders
where my_to_date(wo_dt, 'dd/mm/rr') is null;

or from the underlying table:

select * from mx_wo_data
where my_to_date(substr(uom, 1, 8), 'dd/mm/rr') is null;

If you can't create a function then you can use the same logic in an anonymous block.

It's odd that changing the condition causes the error though, as your (implicit and explicit) conversions are applied before the condition is evaluated, and using the function means any index on that column can't be used; so (in the absence of any other filters) you should be doing a full table scan for both queries, the conversion should be applied to all values in the column before it's filtered, and you should get the error either way. So this doesn't really answer that aspect of the question.

edited May 23 '17 at 11:44

Community

1
1

answered Aug 19 '14 at 11:18

Alex Poole

183,384
11
179
318

I have tried using `WHERE wo_dt >= TRUNC(SYSDATE - 7)`, this too result in `ORA-01843: not a valid` error. – Jacob Aug 19 '14 at 11:21
`max_orders` is a view and I have included its query in my question as Update 1. – Jacob Aug 19 '14 at 11:32
Any technique to find out incorrect data? Because base table is quite huge in numbers. – Jacob Aug 19 '14 at 11:40
There are a few rows which are null based on `to_date(wo_dt, 'dd/mm/rr') is null` condition. Thus these null rows are causing the issue as it cannot perform implicit conversion? – Jacob Aug 19 '14 at 11:46
2

No, if the `wo_dt` value is null (so `uom` is null) then it won't generate this (or any) error. You have a value that has something like `08/19/14` instead of `19/08/14`. – Alex Poole Aug 19 '14 at 11:49
Thanks Alex for the insights, I will try to find out corrupt data. – Jacob Aug 19 '14 at 12:02
As rightly pointed out, there were a few rows in `mm/dd/rr` format. ammoQ's function did help to trace those rows. Thanks – Jacob Aug 19 '14 at 12:17
2

@Polppan - you realised I was using Justin's `my_to_date` function from the linked answer, which is the same idea as ammoQ's function, right? Not a normal `to_date`? Maybe that link was too subtle. ammoQ's certainly does the job too though. – Alex Poole Aug 19 '14 at 12:19
That's correct, I have seen that, in fact usage of that does solve the problem of `ORA-01843`. However I managed to trace those rows and we could ask user to modify date formats and hence I have upvoted ammoQ's answer. Albeit I appreciate your answer and insights. Thanks Alex. – Jacob Aug 19 '14 at 12:21
1

Ooops, I failed to notice the link in your answer, otherwise I wouldn't have posted a similar function :/ – Erich Kitzmueller Aug 19 '14 at 14:04

score 2 · Answer 2 · answered Aug 19 '14 at 11:07

2

Don't convert sysdate to a date! Just use:

WHERE TO_DATE(wo_dt, 'dd/mm/rr') >= trunc(SYSDATE - 7)

answered Aug 19 '14 at 11:07

Gordon Linoff

1,242,037
58
646
786

I have made this change, however when I scroll through the results or if I use this query in report, while paginating through the report pages, I do get `ORA-01843: not a valid` month error. Do you suspect there is something wrong with data? Beacuse this query or report was working without any errors since long. Thanks – Jacob Aug 19 '14 at 11:11

score 2 · Accepted Answer · answered Aug 19 '14 at 12:02

2

I propose you create a stored function to identify the bad rows:

create function invalid_date(p_d in varchar2) return number as
  v_d date;
begin
  v_d := TO_DATE(p_d, 'dd/mm/rr');
  return 0;
exception 
  when others then
    return 1;
end;
/

select * from mx_orders where invalid_date(wo_dt)=1;

answered Aug 19 '14 at 12:02

Erich Kitzmueller

36,381
5
80
102

Stupendous, this does help to trace out erraneous rows, as rightly pointed out by @Alex, there were a few rows in `mm/dd/rr` format. Appreciated. – Jacob Aug 19 '14 at 12:16

ORA-01843: not a valid month When where condition is changed

3 Answers3