sql - Oracle:避免 to_date 中出现 NULL 值

Question

我有一个函数式 select 语句，它有一个 where 子句，在 where 子句中有一个像这样的语句...

to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

但是，如果camp.start_date为NULL或没有行，那么它会抛出异常 -

ORA-01858: a non-numeric character was found where a numeric was expected

camp.start_date 实际上是一个 VARCHAR2，我需要将其转换为日期(是的，我知道它可能应该是一个日期字段，但我没有选项来更改它)。

我尝试过这样的事情......

to_date(NVL(camp.start_date,SYSDATE), 'MM/DD/YYYY') >= 
to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

这仍然给我一个错误。也尝试过

where camp.start_date is not null and to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

同样的问题。解决这个问题的最佳方法是什么？基本上，当camp.start_date不是有效日期时，to_date会爆炸并抛出错误。

Answer 1

如果start_date为NULL，则不会引发异常。

select to_date( null, 'mm/dd/yyyy' ) 
  from dual

是一个完全有效的 SQL 语句，返回 NULL。

您收到的错误强烈暗示 start_date 列中的至少某些行实际上不是您期望格式的字符串或映射到无效日期的字符串(即字符串 '13/35/2007')。您可以编写一个函数来测试字符串是否可以转换为日期并返回转换后的日期或 NULL。然后您可以使用它来代替 to_date。

CREATE OR REPLACE FUNCTION my_to_date( p_str    IN VARCHAR2,
                                       p_format IN VARCHAR2 )
  RETURN DATE
IS
BEGIN
  RETURN to_date( p_str, p_format );
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN NULL;
END;

然后使用my_to_date而不是to_date。这应该可以消除您遇到的错误。不过，您可能需要清理数据以删除无效字符串。