在where子句中使用日期条件时的ORA-01841错误
我需要为客户挑选过期信用卡的数据。如您所知，卡的过期日期仅使用MM格式的月份和YY格式的年份。在我的数据库中，所有的过期日期都以MMYY格式存储，所以我使用TO_DATE(FONDOS.VENCTARJETA，'MMYY')获取日期，然后应用一些条件。
这是我的疑问：
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)这将返回89行，如下所示：
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 ----使用结果，我们注意到在将'MMYY‘日期转换为日期类型列时没有错误。
正如您所看到的，我在where子句中注释了一个条件，如果取消注释行，则得到以下内容：
ORA-01841：(完整)年份必须在-4713到+9999之间，而不是0
这不是我得到这个错误的唯一行为，但这是最简单的，我要向您展示它是如何失败的。
这没有逻辑，我找不到为什么会发生这种事。拜托救救我。谢谢。
回答 3
Stack Overflow用户
发布于 2020-11-01 18:10:52
过滤器条件可以按照Oracle决定的最佳顺序执行。它建议您表中有一些行不能使用该特定格式掩码正确转换为日期，但确实会被联接条件过滤掉。当您包含过滤器时，Oracle可能会看到它可以在您的datos_fondospol表上预先筛选，然后再连接到您的另一个表，此时，每一行都会命中函数。
如果您使用的是至少OracleVersion12.2，则可以使用validate_conversion识别所有包含无法转换为带有该格式掩码的日期的数据的行
select
from datos_fondospol
where validate_conversion(venctarjeta as date, 'MMYY') = 0如果该数据是正确的，但可以安全地忽略，那么您可以使用另一个12.2加法：
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)如果您只在12.1上，那么您可以使用一个with plsql子句自己创建一个类似的函数：
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)如果小于这个值，则可以显式地执行PL/SQL函数并调用它。或者，您可以创建一个case表达式来首先检查字符串的内容。
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)Stack Overflow用户
发布于 2020-11-01 18:09:06
在VENCTARJETA列中，DATOS_FONDOSPOL中至少有一行字符串不能转换为有效日期。如果您查看带有和不带注释条件的查询计划，我的猜测是，当条件存在时，sysdate在CODINTER谓词之前和/或联接之前应用CODINTER谓词，并使用无效的数据过滤行。如果您的计划碰巧在应用to_date函数之前过滤掉了坏数据，那么查询就会看起来很有效。但是，如果有什么原因导致计划发生更改，并且在筛选出坏数据之前应用了to_date函数，那么您将得到一个错误。
Stack Overflow用户
发布于 2020-11-02 01:10:43
已经尝试过用例不确定这是否是您要寻找的
-用样本数据创建表
创建表POLIZA(IDPOL编号，VENCTARJETA日期)；
创建表DATOS_FONDOSPOL(IDPOL编号，X_EXTRA varchar2(10))；
插入POLIZA值(2001年，to_date('0816'，‘MM/YY’)；插入POLIZA值(2002年，to_date('1016'，'MM/YY'))；
插入DATOS_FONDOSPOL值(2002年，'zzzz')；插入DATOS_FONDOSPOL值(2001年，'zzzz')；
下面的选择对我很好，我所做的就是使用TO_CHAR
SELECT
TO_CHAR(POLIZA.VENCTARJETA, 'MMYY')AS F_VENCIMIENTO
FROM
POLZIA POLIZA,
DATOS_FONDOSPOL FONDOS
WHERE
POLIZA.IDPOL = FONDOS.IDPOL
AND TO_CHAR(POLIZA.VENCTARJETA, 'MMYY') <= TO_CHAR(SYSDATE-120, 'MMYY');https://stackoverflow.com/questions/64635020
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号