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

Question

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

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

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

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

Answer 1

呼应托尼的评论，您最好将日期存储在 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 和 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