sql - Receiving ORA-01843: not a valid month error while retrieving data between two dates

Question

I have a column in oracle database table which is Varchar2. In this column I am storing date line 29-1-2021 or 28-12-2020. I want to retrieve data from below query between two dates then I am getting error of Invalid month. How can I resolve this issue ?

SELECT Line_Stop_Id, Function_name, Product_family, line_description, Reason_Category, Reason_detail, 
  Product_item, product_description, request_raised_date, request_raised_time, cm.EMP_NAME as raised_by, Lse.User_Closer_Description,
  Cm1.Emp_Name as Closer_User, Lse.User_Closer_Date, Lse.Final_Closer_Description, Lse.Final_Closer_Date, Lse.Final_Closer_Time,
  Cm2.Emp_Name as Final_Closer, Lse.Resource_Effected ,
  ROUND(24*(sysdate - to_date(Request_Raised_Date
  ||' '
  ||request_raised_time, 'DD-Mon-RR HH24:MI:SS'))) AS TAT
  FROM Xx_Lsp_Linestoppage_Entry lse 
  Left join Emp_Master cm ON Lse.Raised_By = Cm.Emp_No
  Left join Emp_Master cm1 ON Lse.Closer_User = Cm1.Emp_No
  Left join Emp_Master cm2 ON Lse.Final_Closed_By = Cm2.Emp_No
  where TO_DATE(Lse.Request_Raised_Date, 'DD-Mon-RR') Between TO_DATE('01-Jan-21', 'DD-Mon-RR') and TO_DATE('29-Jan-21', 'DD-Mon-RR');

question from:https://stackoverflow.com/questions/65950000/receiving-ora-01843-not-a-valid-month-error-while-retrieving-data-between-two-d

Answer 1

You should check the validity of your date values stored as strings before a conversion and do a cleanup of them (of fix them).

This can be done via, for example, cursor in PL/SQL block (or wrapped in function to export query results or filter by its value):

declare
  l_date date;
begin

  for r in (
    select distinct
      request_raised_time
    from Xx_Lsp_Linestoppage_Entry
    /*To reduce rows in cursor*/
    where not regexp_like('^d{2}-d{2}-d{4}')
  ) loop
  
    begin
      
      l_date := to_date(r.q, 'dd-mm-yyyy');
    exception
      when others then dbms_output.put_line('Invalid date: ' || r.q);
    end;
  
  end loop;
end;
/

db<>fiddle here

Note, that you need to quote dashes inside format to make it exact, because Oracle treats unquoted dash as any symbol from quite wide set of delimiters. So it will process 01/12/2020 as date '2020-12-01', not as invalid date.

select to_date('01/12/2021', 'dd-mm-yyyy') as dt from dual;

DT                   
-------------------- 
2021-12-01T00:00:00Z 


Elapsed: 00:00:00.002
1 rows selected.

select to_date('01-12-2021', 'dd-mm-yyyy') as dt from dual;

DT                   
-------------------- 
2021-12-01T00:00:00Z 


Elapsed: 00:00:00.002
1 rows selected.

select to_date('01$12$2021', 'dd-mm-yyyy') as dt from dual;

DT                   
-------------------- 
2021-12-01T00:00:00Z 


Elapsed: 00:00:00.002
1 rows selected.