CODEWITHSUNDEEP
oracle-databasedate
Venkatesh Kannan
Venkatesh Kannan

Reputation: 35

"ORA-01843: not a valid month" error Oracle SQL

Please help. I making a minor mistake here but I am not able to find it even after reading the previous questions.

SELECT TRUNC (SYSDATE) - TO_DATE (open_date_txt, 'yyyy-mm-dd') AS Days_open
FROM 'table'
WHERE TRUNC (SYSDATE) - TO_DATE (open_date_txt, 'yyyy-mm-dd') > 90 AND
      TRUNC (SYSDATE) - TO_DATE (open_date_txt, 'yyyy-mm-dd') < 120

The query runs without an error but when I select all the data from the results(by doing ctrl+A), I get ORA-1843: not a valid month.

NLS_DATE_FORMAT is DD-MON-RR NLS_DATE_LANGUAGE is AMERICAN

The table has 2 columns, and the open_date_txt field is stored as varchar2(10) in the database. Please see below

Name    open_date_txt
AA      2010-03-24
BB      2010-07-23
CC      2015-09-20
DD      1997-03-25

Thank you.

Also,I'm struck with incorrect data model. This date field has values like '0--' , month values greater than 12 , and finally days greater than 31. Now I want to ignore these records during my select. The stored procedures I created doesn't work fine, any help would be greatly appreciated. Thanks.

Upvotes: 1

Views: 2531

Answers (1)

Dave Costa
Dave Costa

Reputation: 48131

The error implies that there is at least one row in the table where the value in open_date_txt cannot be converted to a DATE using the given format string.

The query will run until it hits that row, then throw the error. Of course, you never see the row that causes the error in your output, so it is hard to find the value causing the problem.

One technique is to go through row by row and raise a more useful exception that identifies the entry causing the error. For example:

DECLARE
  d DATE;
BEGIN
  FOR x IN (SELECT name, open_date_txt FROM table) LOOP
    BEGIN
      d := TO_DATE( x.open_date_txt, 'yyyy-mm-dd');
    EXCEPTION
      WHEN others THEN
        raise_application_error(-20000, 'Error on name: ' || x.name, TRUE );
    END;
  END LOOP;
END;

You could also modify this to print out the name for the row and continue, so you'd find if there are multiple rows with bad data.

Upvotes: 1

Related Questions