SQL Oracle：避免在 to_date 中使用 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22336983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Avoiding NULL value in to_date
提问by HelloWorld
I have a functional select statement that has a where clause, in the where clause there is a statement like so...
我有一个功能选择语句，它有一个 where 子句，在 where 子句中有一个像这样的语句......
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')
However, if camp.start_date is NULL or has no rows then it is throwing an exception -
但是，如果 camp.start_date 为 NULL 或没有行，则它会抛出异常 -
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: 在需要数字的地方发现了非数字字符
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).
camp.start_date 实际上是一个 VARCHAR2，我需要将其转换为日期，（是的，我知道它可能应该是一个日期字段，但我没有更改它的选项）。
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
这仍然给我一个错误。也试过
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 不为 null 并且 to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')
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.
同样的问题。解决这个问题的最佳方法是什么？当 camp.start_date 不是有效日期时，基本上 to_date 会爆炸并抛出错误。
回答by Justin Cave
If start_dateis NULL, no exception is thrown.
如果start_date为 NULL，则不抛出异常。
select to_date( null, 'mm/dd/yyyy' )
from dual
is a perfectly valid SQL statement that returns NULL.
是一个完全有效的 SQL 语句，它返回 NULL。
The error you are getting strongly implies that at least some of the rows in the start_datecolumn 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.
您收到的错误强烈暗示该列中至少有一些行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;
and then use my_to_dateinstead 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.
然后使用my_to_date代替to_date。这应该消除你得到的错误。不过，您可能希望清理数据以去除无效字符串。
回答by venkatesh
You can use like this:
你可以这样使用：
NVL(TO_CHAR(SYSDATE,'DD-MM-YYYY'),' ')
This will convert the date to string format and then to empty string.
这会将日期转换为字符串格式，然后转换为空字符串。
回答by Clint
You need to convert sysdate to the valid char string format:
您需要将 sysdate 转换为有效的字符字符串格式：
to_date(NVL(start_date,to_char(SYSDATE,'MM/DD/YYYY')), 'MM/DD/YYYY') >=
to_date(from_date, 'YYYY-MM-DD HH24:MI:SS')
回答by Vincent Quiles
Does
做
NVL(TO_DATE(start_date,'MM/DD/YYYY'),SYSDATE) >= from_date
make more sence if from_datehas a not null date type value?
If from_datecan be null or is not a date value then do this?
如果from_date具有非空日期类型值，则更有意义？如果from_date可以为 null 或不是日期值，则执行此操作？
NVL(TO_DATE(start_date,'MM/DD/YYYY'),SYSDATE) >= NVL(TO_DATE(from_date,'MM/DD/YYYY'),SYSDATE)
Something like comparing apples to apples?
像比较苹果和苹果之类的东西？
