oracle 与日期比较时忽略 WHERE 子句中的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6100408/
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
ignoring timestamp in WHERE clause when comparing to date
提问by Omnipresent
My table has records like these
我的桌子上有这样的记录
23-MAY-11 11.40.39.000000 AM
23-MAY-11 11.40.39.000000 AM
The following query brings nothing
下面的查询什么也没带来
SELECT *
FROM my_table
WHERE tenant_pha = 'test'
AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
AND create_date <= TO_DATE('05/23/2011','mm/dd/yyyy')
However, the below query will bring data
但是，下面的查询将带来数据
SELECT *
FROM my_table
WHERE tenant_pha = 'test'
AND create_date >= TO_DATE('05/10/2011','mm/dd/yyyy')
AND create_date <= TO_DATE('05/24/2011','mm/dd/yyyy')
I think this is because create_datecolumn is time stamp.
我认为这是因为create_date列是时间戳。
How can I change my query to bring the desired result ( I want to avoid doing functions on the left side columns because they will make the query long).
如何更改我的查询以带来所需的结果（我想避免在左侧列上执行函数，因为它们会使查询变长）。
回答by Geoff
You are right about the timestamp. '05/23/2011' is the same as '05/23/2011 12:00 AM'.
你是对的时间戳。“05/23/2011”与“05/23/2011 12:00 AM”相同。
To include the whole day I usually move my date up by a day. < '05/24/2011' will include all of 5/23.
为了包括一整天，我通常将日期提前一天。< '05/24/2011' 将包括所有 5/23。
or change to '05/23/2011 23:59:59'
或更改为“05/23/2011 23:59:59”
回答by a_horse_with_no_name
You can use trunc() without problems, you only need to create a function based index.
您可以毫无问题地使用 trunc()，您只需要创建一个基于函数的索引。
If you create this index:
如果您创建此索引：
CREATE INDEX idx_trunc_date ON my_table (trunc(create_date));
then the following condition will make use of that index:
那么以下条件将使用该索引：
AND trunc(create_date) >= TO_DATE('05/10/2011','mm/dd/yyyy')
