SQL 通过只知道没有时间的日期从表中选择 (ORACLE)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2399753/
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
Select from table by knowing only date without time (ORACLE)
提问by Abdulrhman
I'm trying to retrieve records from table by knowing the date in column contains date and time.
我试图通过知道列中的日期包含日期和时间来从表中检索记录。
Suppose I have table called t1which contains only two column nameand daterespectively.
假设我有表名为t1其中只包含两个列name和date分别。
The data stored in column date like this 8/3/2010 12:34:20 PM.
像这样存储在日期列中的数据8/3/2010 12:34:20 PM。
I want to retrieve this record by this query for example (note I don't put the time):
例如，我想通过此查询检索此记录（注意我没有输入时间）：
Select * From t1 Where date="8/3/2010"
This query give me nothing !
这个查询没有给我任何东西！
How can I retrieve dateby knowing only datewithout the time?
我如何date通过只知道date而没有时间来检索？
回答by Peter Lang
DATEis a reserved keyword in Oracle, so I'm using column-name your_dateinstead.
DATE是 Oracle 中的保留关键字，因此我使用 column-name your_date。
If you have an index on your_date, I would use
如果你有一个索引your_date，我会用
WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
AND your_date < TO_DATE('2010-08-04', 'YYYY-MM-DD')
or BETWEEN:
或BETWEEN：
WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
If there is no index or if there are not too many records
如果没有索引或者没有太多记录
WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')
should be sufficient. TRUNCwithout parameter removes hours, minutes and seconds from a DATE.
应该足够了。TRUNC不带参数从一个中删除小时、分钟和秒DATE。
If performance really matters, consider putting a Function Based Indexon that column:
如果性能真的很重要，请考虑Function Based Index在该列上放置一个：
CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));
回答by JavaSheriff
Convert your date column to the correct format and compare:
将日期列转换为正确的格式并进行比较：
SELECT * From my_table WHERE to_char(my_table.my_date_col,'MM/dd/yyyy') = '8/3/2010'
This part
这部分
to_char(my_table.my_date_col,'MM/dd/yyyy')
Will result in string '8/3/2010'
将导致字符串'8/3/2010'
回答by R. Genaro
Personally, I usually go with:
就个人而言，我通常会选择：
select *
from t1
where date between trunc( :somedate ) -- 00:00:00
and trunc( :somedate ) + .99999 -- 23:59:59
回答by Dustin Laine
You could use the between function to get all records between 2010-08-03 00:00:00:000 AND 2010-08-03 23:59:59:000
您可以使用 between 函数获取 2010-08-03 00:00:00:000 和 2010-08-03 23:59:59:000 之间的所有记录
回答by Dave Kuziara
trunc(my_date,'DD')will give you just the date and not the time in Oracle.
trunc(my_date,'DD')只会给你日期而不是 Oracle 中的时间。
回答by Oragon
Simply use this one:
只需使用这个：
select * from t1 where to_date(date_column)='8/3/2010'
回答by kiruthika
Try the following way.
试试下面的方法。
Select * from t1 where date(col_name)="8/3/2010"
