Oracle：在to_date中避免使用NULL值 [英] Oracle: Avoiding NULL value in to_date

问题描述

我有一个功能选择语句，其中有一个where子句，在where子句中有一个这样的语句...

I have a functional select statement that has a where clause, in the where clause there is a statement like so...

to_date（camp.start_date，'MM / DD / YYYY'）> = to_date（：from_date，
'YYYY-MM-DD HH24：MI：SS'）

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

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

However, if camp.start_date is NULL or has no rows then it is throwing an exception -

ORA-01858：找到一个非数字字符，其中一个数字是
。

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

camp.start_date实际上是一个VARCHAR2，我需要转换成一个日期，（是的，我知道它可能应该是一个日期字段，但我没有选项来改变这个）。

camp.start_date is actually a VARCHAR2 that I need to convert into a date, (yes I know it probably should be a date field but I don't have the options to change this).

我尝试过这样的东西...

I tried something like this...

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

哪些还是给我一个错误。还试过

Which still is giving me an error. Also tried

其中camp.start_date不为null，to_date（camp.start_date，
'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')

同样的问题。这是最好的方法是什么？基本上to_date是在camp.start_date不是有效日期时爆炸和抛出错误。

same issue. What is the best way around this? Basically to_date is exploding and throwing an error when camp.start_date is not a valid date.

推荐答案

如果 start_date 为NULL，不会抛出异常。

If start_date is NULL, no exception is thrown.

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

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

is a perfectly valid SQL statement that returns NULL.

您获得的错误强烈意味着 start_date 列中的至少一些行实际上不是字符串以您期望的格式或映射到无效日期（即字符串'13 / 35/2007'）。您可以编写一个函数来测试是否可以将字符串转换为日期并返回转换的日期或NULL。您可以使用它，而不是 to_date 。

The error you are getting strongly implies that at least some of the rows in the start_date column are not actually strings in the format you expect or that map to invalid dates (i.e. the string '13/35/2007'). You can write a function that tests to see whether a string can be converted to a date and return either the converted date or a NULL. You can then use that instead of 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 。这样可以消除你所遇到的错误。但是，您可能想要清理数据，以摆脱无效的字符串。

and then use my_to_date instead of to_date. That should eliminate the error you're getting. You'll probably want to clean up the data, though, to get rid of the invalid strings.

这篇关于Oracle：在to_date中避免使用NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！