Oracle Date Error - ORA-01841

Question

I have the following table in Oracle11g.

SQL> DESC tmp_test;
Name                 Type          Nullable Default Comments 
-------------------- ------------- -------- ------- -------- 
SERNO                NUMBER(10)                              
CARDNO               VARCHAR2(25)  Y                         
COL_A                VARCHAR2(255) Y                         
DATEA                DATE          Y                         
DATEB                DATE          Y                         
TAG                  VARCHAR2(255) Y                         
FEEDBACK             CHAR(1)       Y


SQL> 
SQL> SELECT * FROM (SELECT T.COL_A FROM TEMP_TEST T ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <=10;
COL_A
--------------------------------------------------------------------------------
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150205-0000
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150213-0000
00 OK.20150301-0000
00 OK.20150129-0000
00 OK.20150301-0000
10 rows selected

SQL>

I am attempting to identify all rows in table TEMP_TEST where DATE in COL_A is less than SYSDATE - 7.

SQL>
SQL> SELECT * FROM TEMP_TEST T WHERE
TO_DATE(SUBSTR(TRIM(T.COL_A),7,8),'YYYYMMDD') < sysdate-7;
**ORA-01841: (full) year must be between -4713 and +9999, and not be 0**
SQL>

The table only contain 200 rows, so I have visually checked for any issues with the data. All dates are valid. What might be the cause of this error?

Thanks

Can you try adding `nls_date_language` to the `to_date`? Try this `SELECT TO_DATE(SUBSTR(TRIM('00 OK.20150301-0000'),7,8),'YYYYMMDD', 'NLS_DATE_LANGUAGE = AMERICAN') FROM dual;` — Lalit Kumar B, Mar 03 '15 at 11:01
If I were you, I'd be doing `select SUBSTR(TRIM(T.COL_A),7,8),'YYYYMMDD' from temp_test` and then checking what is returned. Usually that's enough to identify problematic rows. — Boneist, Mar 03 '15 at 11:03
SELECT TO_DATE(SUBSTR(TRIM('00 OK.20150301-0000'),7,8),'YYYYMMDD', 'NLS_DATE_LANGUAGE = AMERICAN') FROM dual; 1 row selected. OK select SUBSTR(TRIM(T.COL_A),7,8),'YYYYMMDD' from temp_test -- all 270 rows selected. — MrM, Mar 03 '15 at 11:55

Bacs · Accepted Answer · 2015-03-03T12:11:22.870

2

Apparently all dates are not valid, hence the error you're getting. I would try something like the following (untested, but think it's ok), just to identify the problem records.

declare 
  v_date date;
begin
  for c in (select col_a from temp_test) loop
    begin
      v_date := to_date(substr(trim(c.col_a),7,8),'YYYYMMDD');
    exception when others then
      dbms_output.put_line(c.col_a);
    end;
  end loop;
end;

Note that this use of others as the only exception handler would generally be considered poor practice. In production code, exceptions should be handled individually. Even when used for debugging, it would be better to output the SQL error, but for a first pass where only a few errors are expected, sometimes it's ok just to be lazy.

edited Mar 03 '15 at 12:11

answered Mar 03 '15 at 11:16

Bacs

919
1
10
16

When others will catch all the errors, however, you are supposed to catch `ORA-01841`. So why `others`? `when others` is itself a bug. – Lalit Kumar B Mar 03 '15 at 11:34
Like I said, it's lazy. I wouldn't do this in production code. In this context, we know we're dealing with a date conversion error, and we know we've at most 200 values to check. If anything, it would be pedantic to handle all possible exceptions indiviually here. – Bacs Mar 03 '15 at 11:43
Thank for adding that line, I just thought that someone blindly copying the code should know the use of it. I commented on the other answer as well. It is constructive, and I appreciate your reply. – Lalit Kumar B Mar 03 '15 at 11:54
1

Thanks Lalit, and you're quite right - I'll amend my answer accordingly. – Bacs Mar 03 '15 at 12:00
Now for the complete answer, +1 – Lalit Kumar B Mar 03 '15 at 12:16

Frank Schmitt · Answer 2 · 2015-03-03T12:10:09.957

2

Using an explicit cursor for debugging number or date conversion errors usually quickly turns up the offending rows (here, I faked your TEMP_TEST table):

declare
  cursor l_cur is
    with temp_test(col_a) as (
      select '20150201_abc' col_a from dual union all
      select 'x0150201_abc' col_a from dual union all
      select '20150201_abc' col_a from dual)      
    SELECT * FROM TEMP_TEST T;

  l_data l_cur%rowtype;
  dummy  date;
begin
  open l_cur;
  loop
    fetch l_cur
      into l_data;
    exit when l_cur%notfound;
    begin
      dummy := TO_DATE(SUBSTR(TRIM(l_data.COL_A), 1, 8), 'YYYYMMDD');
    exception
      when others then
        dbms_output.put_line(sqlerrm || ' for ' || l_data.col_a);
    end;
  end loop;
end;

EDIT: WHEN OTHERS is used here solely for interactive debugging purposes - please don't use it in production code.

edited Mar 03 '15 at 12:10

answered Mar 03 '15 at 11:20

Frank Schmitt

30,195
12
73
107

When others will catch all the errors, however, you are supposed to catch `ORA-01841`. So why `others`? `when others` is itself a bug. – Lalit Kumar B Mar 03 '15 at 11:34
Modified cusor . cursor l_cur is select COL_A from TEMP_TEST; No rows fetched. Confirmed by extracting all rows With SQL SELECT TO_DATE(SUBSTR(TRIM(T.COL_A),7,8),'YYYYMMDD') FROM TEMP_TEST T There is not an issue With my data – MrM Mar 03 '15 at 11:49
This is a script that is used just for interactive debugging purposes. Using `WHEN OTHERS` *in production code* is almost always a bug (but there are circumstances where it makes sense, e.g. logging the error and re-raising it). – Frank Schmitt Mar 03 '15 at 11:49
1

Lalit, I accept your criticism on my answer, though I disagree with it in this context. I don't accept it at all on this answer. The use of `others` is perfectly valid here, because it's used in conjunction with outputting the `sqlerrm` for help debugging. – Bacs Mar 03 '15 at 11:49
1

Frank, firstly I hope you didn't get me wrong, I just added that comment so that nobody copies your code blindly and use it, at least by reading that comment and then reading your reply will make some sense. It was all constructive. So, thanks for replying which has given a complete sense to the entire commenting thing. Much appreciated! – Lalit Kumar B Mar 03 '15 at 11:53
@Lalit Don't worry, I always welcome (constructive) criticism. I've updated my answer accordingly. – Frank Schmitt Mar 03 '15 at 12:10
Now for the complete answer, +1 – Lalit Kumar B Mar 03 '15 at 12:17

Oracle Date Error - ORA-01841

2 Answers2