I need to pick the data of expired credit cards for customers. As you know, the expiration date of a card only uses month in MM format and year in YY format. In my database all of the expiration dates are stored in MMYY format, so I use TO_DATE(FONDOS.VENCTARJETA, 'MMYY') to get a date and then to apply some conditions.
This is my Query:
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, 'MMYY') <= SYSDATE AND
POLIZA.CODINTER = TO_NUMBER(:P2_CLAVE)
This is returning 89 rows like these:
F_VENCIMIENTO |
-------------------|
|
2023-08-01 00:00:00|
2020-08-01 00:00:00|
2021-11-01 00:00:00|
2020-09-01 00:00:00|
|
2023-02-01 00:00:00|
---- many more ----
With the results, we notice there are no errors when converting 'MMYY' date into a date type column.
As you can see, I commented a condition in where clause, then if I uncomment the line I get this:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
This is not the only behaviour where I am getting this error, but this was the simplest I got to show you how it fails.
It has no logic, I can't find why it happens. Please, help. Thanks.
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)