如何处理 SELECT 语句中的 to_date 异常以忽略这些行？

我有以下查询，我试图将其用作我正在处理的水晶报告中的命令。

SELECT * FROM myTable
WHERE to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}

这工作正常，但我唯一担心的是日期可能并不总是采用正确的格式（由于用户错误）。我知道当 to_date 函数失败时它会抛出异常..是否可以以忽略 SELECT 语句中相应行的方式处理此异常？否则，如果整个数据库中只有一个日期格式不正确，我的报告就会中断。

我查看了 Oracle 是否提供了 isDate 函数，但似乎您应该只处理异常。任何帮助将不胜感激。谢谢！！

与 Tony 的评论相呼应，您最好将日期存储在 DATE 列中，而不是强制前端查询工具查找和处理这些异常。

但是，如果您遇到不正确的数据模型，早期版本中最简单的选择是创建一个执行转换并处理错误的函数，

CREATE OR REPLACE FUNCTION my_to_date( p_date_str IN VARCHAR2,
                              p_format_mask IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_str, p_format_mask );
  RETURN l_date;
EXCEPTION
  WHEN others THEN
    RETURN null;
END my_to_date;

然后你的查询将变成

SELECT * 
  FROM myTable
 WHERE my_to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}

当然，您很可能想要一个基于函数的索引MY_TO_DATE调用以使该查询相当有效。

在 12.2 中，Oracle 添加了扩展to_date and cast处理错误转换的函数

SELECT * 
  FROM myTable
 WHERE to_date(myTable.sdate default null on conversion error, 'MM/dd/yyyy') <= {?EndDate}

您还可以使用validate_conversion如果您正在查找所有有效（或无效）日期的行，请使用此函数。

SELECT *
  FROM myTable 
 WHERE validate_conversion( myTable.sdate as date, 'MM/DD/YYYY' ) = 1