ORA-01841 Error when aplying conditions on date in where clause

The filter conditions can be executed in any order Oracle decides is best. It suggests that you have some rows in your table that do not properly convert to a date using that particular format mask, but do get filtered out by your join condition. When you include the filter, Oracle probably sees that it can prefilter on your datos_fondospol table before joining to your other table, at which point every row will hit the function.

If you are on at least Oracle version 12.2 you can identify all the rows that contain data that can’t be converted to a date with that format mask with validate_conversion:

select 
from   datos_fondospol 
where  validate_conversion(venctarjeta as date, 'MMYY') = 0

If this data is correct but can safely be ignored then you can use another 12.2 addition:

SELECT 
    TO_DATE(FONDOS.VENCTARJETA, 'MMYY') AS F_VENCIMIENTO
FROM 
    POLIZA POLIZA,
    DATOS_FONDOSPOL FONDOS
WHERE
    POLIZA.IDEPOL = FONDOS.IDEPOL AND
    TO_DATE(FONDOS.VENCTARJETA default null on conversion error, 'MMYY') <= SYSDATE AND
    POLIZA.CODINTER = TO_NUMBER(:P2_CLAVE)

If you are only on 12.1 then you can make a similar function yourself with a with plsql clause:

with 
 function default_date(dateString varchar2,dateFormat varchar2)
 return date
 is
   convertedDate  date;
 begin
   convertedDate := to_date(dateString,dateFormat );
   return convertedDate ;
 exception when others then
   return null;
 end;
SELECT 
    TO_DATE(FONDOS.VENCTARJETA, 'MMYY') AS F_VENCIMIENTO
FROM 
    POLIZA POLIZA,
    DATOS_FONDOSPOL FONDOS
WHERE
    POLIZA.IDEPOL = FONDOS.IDEPOL AND
    default_date(FONDOS.VENCTARJETA, 'MMYY') <= SYSDATE AND
    POLIZA.CODINTER = TO_NUMBER(:P2_CLAVE)

If you are on less than that then you can make the PL/SQL function explicitly and call it. Or you could manufacture a case expression to check the contents of your string first.

SELECT 
    TO_DATE(FONDOS.VENCTARJETA, 'MMYY') AS F_VENCIMIENTO
FROM 
    POLIZA POLIZA,
    DATOS_FONDOSPOL FONDOS
WHERE
    POLIZA.IDEPOL = FONDOS.IDEPOL AND
    case when regexp_like (FONDOS.VENCTARJETA, '^[0-9]{4}$')
 and to_number(substr(FONDOS.VENCTARJETA,1,2)) between 1 and 12  
 then to_date(FONDOS.VENCTARJETA, 'MMYY') else cast(null as date) end <= SYSDATE AND
    POLIZA.CODINTER = TO_NUMBER(:P2_CLAVE)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top