Oracle 11g 中的日期比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8706276/
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
Date comparison in Oracle 11g
提问by Mike
I'm new to Oracle 11g & I have a question about a query.
我是 Oracle 11g 的新手，我有一个关于查询的问题。
I have a table dummywhich has created_datecolumn of type Date.
我有一个表dummy，其中包含created_date类型为 的列Date。
I want a query which will return all the records where created_date+ 7 days is less than today's date.
我想要一个查询，它将返回created_date+ 7 天小于今天日期的所有记录。
What type of query in Oracle 11g accomplishes this?
Oracle 11g 中哪种类型的查询可以实现这一点？
回答by rejj
Oracle lets you use +for date arithmetic, so
Oracle 允许您使用+日期算术，因此
where table.created_date >= sysdate
and table.created_date < sysdate + 7
will find rows between exactly now and exactly now plus 7 days.
将查找恰好在现在和现在加上 7 天之间的行。
If you don't want to include the time component, you can use the trunc()function
如果不想包含时间组件，可以使用该trunc()函数
where trunc(table.created_date) >= trunc(sysdate)
and trunc(table.created_date) < trunc(sysdate) + 7
回答by eaolson
I think rejj's solution will give you the records between now and seven days in the future. From your description, it sounds like you probably want those records within the past seven days. I'd do this as:
我认为 rejj 的解决方案将为您提供现在到未来 7 天之间的记录。根据您的描述，您可能想要过去 7 天内的记录。我会这样做：
WHERE created_date <= SYSDATE
AND created_date >= SYSDATE - 7
This may be more clear, and is equivalent:
这可能更清楚，并且是等效的：
WHERE created_date BETWEEN SYSDATE AND (SYSDATE - 7)
Be aware that using TRUNC() will cause the optimizer to bypass any indexes you have on created_date, unless you have a functional index defined.
请注意，除非您定义了功能索引，否则使用 TRUNC() 将导致优化器绕过您在 created_date 上拥有的任何索引。
回答by Bob Jarvis - Reinstate Monica
Using INTERVAL constants can make date arithmetic clearer, as in
使用 INTERVAL 常量可以使日期算术更清晰，如
SELECT *
FROM DUMMY
WHERE CREATED_DATE >= TRUNC(SYSDATE) - INTERVAL '7' DAY
Share and enjoy.
分享和享受。
