Oracle - 从日期字符串转换时在两个日期（含）之间选择的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7385217/
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 - selected records between two dates (inclusive) when converting from date string
提问by ale
I have the following Oracle query
我有以下 Oracle 查询
SELECT *
FROM table
WHERE date_opened
BETWEEN ((TO_DATE('2011-08-01', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
AND ((TO_DATE('2011-08-31', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
that nearly works but it doesn't include the dates records that are dated 2011-08-31. Any ideas? It has probably got something to do with how I am converting my date strings...
这几乎有效，但不包括日期为 2011-08-31 的日期记录。有任何想法吗？这可能与我如何转换日期字符串有关...
UPDATE: I really should have said that the date is actually a UNIX timestamp. That is why I am using the 86400 and 01-JAN-1970
更新：我真的应该说日期实际上是一个 UNIX 时间戳。这就是我使用 86400 和 01-JAN-1970 的原因
Thank you :)
谢谢 ：）
回答by Lukas Eder
If the upper bound of an interval is not included in your results, then it's likely that you're building an "exclusive" filter with respect to the upper bound. So just add one day to the upper bound. I.e.
如果您的结果中不包含区间的上限，那么您很可能正在构建一个关于上限的“独占”过滤器。因此，只需将一天添加到上限即可。IE
AND ((TO_DATE(...) - to_date(...) + 1) * (86400)) - 1
In Oracle, +1will add one day when used in date time arithmetic.
在 Oracle 中，+1用于日期时间运算时会加一天。
Note: BETWEEN .. ANDcreates an inclusive filter, as Ollie stated, but your arithmetic may change that behaviour by transforming things to seconds
注意：BETWEEN .. AND正如 Ollie 所说，创建一个包含过滤器，但您的算术可能会通过将事物转换为秒来改变该行为
回答by Klas Lindb?ck
You don't include anything that happened after midnight the last day.
您不包括最后一天午夜之后发生的任何事情。
try:
尝试：
AND ((TO_DATE('2011-09-01', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) - 1)
