oracle 搜索查询中的空日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5237544/
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
null date in search query
提问by gswanson
this is a easy question but I can't think of how to do this at the moment.
这是一个简单的问题，但我现在想不出如何做到这一点。
I have a date field in search query. The query isn't dynamic or this would be easier. I need to be able to return the records that match the date entered or if the date isn't entered then it should return all.
我在搜索查询中有一个日期字段。查询不是动态的，否则会更容易。我需要能够返回与输入的日期匹配的记录，或者如果未输入日期，则它应该返回全部。
This is what I have but it isn't working. It's not returning any rows, when there is criteria or when there isn't.
这就是我所拥有的，但它不起作用。当有条件或没有条件时，它不会返回任何行。
AND ( (table.dateField = p_dateField)
OR (table.dateField = table.dateField and table.dateField is null))
thanks in advance.
提前致谢。
After an hour of working with it I came up with this:
经过一个小时的工作，我想出了这个：
and (
( p_dateField IS NOT NULL AND table.dateField = p_dateField)
OR ( p_dateField IS NULL AND (table.dateField is null or (table.dateField is not null))
)
It works for the few tests I've been able to run against it. If anyone can suggest a better method please do.
它适用于我能够针对它运行的少数测试。如果有人可以建议更好的方法，请这样做。
Thanks!
谢谢！
回答by Simen S
Have you tried:
你有没有尝试过：
AND ( (table.dateField = p_dateField AND NOT(p_dateField is NULL))
OR p_dateField is NULL)
AND ( (table.dateField = p_dateField AND NOT(p_dateField is NULL))
OR p_dateField is NULL)
I am assuming that p_dateField is the Date parameter
我假设 p_dateField 是 Date 参数
回答by jachguate
just write your where condition like this:
只需像这样写下你的 where 条件：
where previous_conditions
AND ((table.dateField = p_dateField) or (p_dateField is null))
and other_conditions;
回答by DCookie
How about:
怎么样：
AND table.dateField = NVL(pdateField, table.dateField)
Assuming table.dateField does not contain nulls. If it does, then perhaps:
假设 table.dateField 不包含空值。如果是这样，那么也许：
AND NVL(table.dateField, SYSDATE+10000) =
NVL(pdateField, NVL(table.dateField, SYSDATE+10000)
Assumes that SYSDATE+10000 is a value not in your data.
假设 SYSDATE+10000 不是您的数据中的值。
回答by Lost in Alabama
I would try using an NVL clause in the OR section:
我会尝试在 OR 部分使用 NVL 子句：
AND ( (table.dateField = p_dateField)
OR (NVL(p_datefield, to_date('01/01/1901','MM/DD/YYYY')) =
to_date('01/01/1901,'MM/DD/YYYY')))
