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

2021-12-06 00:00:00 crystal-reports oracle

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

I have the following query that I am attempting to use as a COMMAND in a crystal report that I am working on.

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

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

This works fine, however my only concern is that the date may not always be in the correct format (due to user error). I know that when the to_date function fails it throws an exception.. is it possible to handle this exception in such a way that it ignores the corresponding row in my SELECT statement? Because otherwise my report would break if only one date in the entire database is incorrectly formatted.

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

I looked to see if Oracle offers an isDate function, but it seems like you are supposed to just handle the exception. Any help would be greatly appreciated. Thanks!!

推荐答案

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

Echoing Tony's comment, you'd be far better off storing dates in DATE columns rather than forcing a front-end query tool to find and handle these exceptions.

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

If you're stuck with an incorrect data model, however, the simplest option in earlier versions is to create a function that does the conversion and handles the error,

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 调用上有一个基于函数的索引，以便使这个查询相当有效.

Of course, you'd most likely want a function-based index on the MY_TO_DATE call in order to make this query reasonably efficient.

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

In 12.2, Oracle has added extensions to the to_date and cast functions to handle conversions that error

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

如果您要查找所有是(或不是)有效日期的行，您也可以使用 validate_conversion 函数.

You could also use the validate_conversion function if you're looking for all the rows that are (or are not) valid dates.

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

相关文章