SQL Oracle 在 DATE 使用 LIKE '%'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33151961/
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 use LIKE '%' on DATE
提问by Mihawk
My table myTabhas the column startDate, which has the datatype "DATE". The data in this column are stored like dd.mm.yyyy.
我的表myTab有列startDate，其数据类型为“DATE”。此列中的数据存储为dd.mm.yyyy.
Now I'm trying to get data with this query:
现在我正在尝试使用此查询获取数据：
SELECT * FROM myTab WHERE startDate like '%01.2015"
Somehow it doesn't work and I don't know why.
不知何故它不起作用，我不知道为什么。
Hope someone can help.
希望有人能帮忙。
回答by Guffa
To make a text search on the date you would have to convert the date to text.
要对日期进行文本搜索，您必须将日期转换为文本。
It's more efficient if you calculate the first and last date for what you want to find and get everything between them. That way it's done as numeric comparisons instead of a text pattern match, and it can make use of an index if there is one:
如果您计算要查找的内容的第一个和最后一个日期并获取它们之间的所有内容，则效率会更高。这样，它是作为数字比较而不是文本模式匹配来完成的，如果有索引，它可以使用索引：
SELECT * FROM myTab WHERE startDate >= DATE '2015-01-01' AND startDate < DATE '2015-02-01'
回答by tib
SELECT * FROM myTab WHERE TO_CHAR(startDate,'dd.mm.yyyy') LIKE '%01.2015'
回答by mbk
If the field type is "DATE" then the value isn't stored as a string, it's a number managed by Oracle, so you have to convert it to a string:
如果字段类型为“DATE”，则该值不会存储为字符串，而是由 Oracle 管理的数字，因此您必须将其转换为字符串：
SELECT * FROM myTab WHERE to_char(startDate, 'MM.YYYY') = '01.2015';
You can also use date ranges in SQL queries:
您还可以在 SQL 查询中使用日期范围：
SELECT * FROM myTab
WHERE startDate
BETWEEN to_date('01.01.2015', 'DD.MM.YYYY')
AND to_date('31.01.2015', 'DD.MM.YYYY');
回答by Wernfried Domscheit
Regarding you actual question "Somehow it doesn't work and I don't know why."
关于你的实际问题“不知何故它不起作用，我不知道为什么。”
Oracle make an implicit conversion from DATEto VARHCAR2, however it uses the default NLS_DATE_FORMATwhich is probably different to what you use in your query.
Oracle 进行了从DATEto的隐式转换VARHCAR2，但是它使用的默认值NLS_DATE_FORMAT可能与您在查询中使用的不同。
回答by Lalit Kumar B
The data in this column are stored like dd.mm.yyyy.
此列中的数据存储为 dd.mm.yyyy。
Oracledoes not store date in the format you see. It stores it internally in proprietary format in 7 bytes with each byte storing different components of the datetime value.
Oracle不会以您看到的格式存储日期。它以专有格式在内部以 7 个字节存储它，每个字节存储日期时间值的不同组成部分。
WHERE startDate like '%01.2015"
WHERE startDate 像“%01.2015”
You are comparing a DATEwith a STRING, which is pointless.
您正在将DATE与STRING进行比较，这是毫无意义的。
From performance point of view, you should use a date range conditionso that if there is any regular INDEXon the date column, it would be used.
从性能的角度来看，您应该使用日期范围条件，以便如果日期列上有任何常规INDEX，它将被使用。
SELECT * FROM table_name WHERE date_column BETWEEN DATE '2015-01-01' AND DATE '2015-02-01'
To understand why a Date range conditionis better in terms of performance, have a look at my answer here.
要了解为什么日期范围条件在性能方面更好，请在此处查看我的答案。
回答by Eronildo Barbosa
I solved my problem that way. Thank you for suggestions for improvements. Example in C#.
我就是这样解决了我的问题。感谢您提出改进建议。C# 中的示例。
string dd, mm, aa, trc, data;
dd = nData.Text.Substring(0, 2);
mm = nData.Text.Substring(3, 2);
aa = nData.Text.Substring(6, 4);
trc = "-";
data = aa + trc + mm + trc + dd;
"Select * From bdPedidos Where Data Like '%" + data + "%'";
回答by alvalongo
To provide a more detailed answer and address this https://stackoverflow.com/a/42429550/1267661answer's issue.
提供更详细的答案并解决这个https://stackoverflow.com/a/42429550/1267661答案的问题。
In Oracle a column of type "date" is not a number nor a string, it's a "datetime" value with year, month, day, hour, minute and seconds. The default time is always midnight "00:00:00"
在 Oracle 中，“日期”类型的列既不是数字也不是字符串，而是带有年、月、日、小时、分钟和秒的“日期时间”值。默认时间总是午夜“00:00:00”
The query:
查询：
Select * From bdPedidos Where Data Like '%" + data + "%'"
won't work in all circumstances because a date column is not a string, using "like" forces Oracle to do a conversion from date value to string value. The string value may be year-month-day-time or month-day-year-time or day-month-year-time, that all depends how a particular Oracle instance has set the parameter NLS_DATE_FORMAT to show dates as strings.
不会在所有情况下都有效，因为日期列不是字符串，使用“like”会强制 Oracle 进行从日期值到字符串值的转换。字符串值可能是年-月-日-时间或月-日-年-时间或日-月-年-时间，这完全取决于特定 Oracle 实例如何设置参数 NLS_DATE_FORMAT 以将日期显示为字符串。
The right way to cover all the possible times in a day is:
涵盖一天中所有可能时间的正确方法是：
Select *
From bdPedidos
Where Data between to_date('" + data + " 00:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date('" + data + " 23:59:59','yyyy-mm-dd hh24:mi:ss')
回答by Steven Toyama
SELECT * FROM myTab WHERE startDate like '%-%-2015';
This will search for all dates in 2015. If this doesn't work, try:
这将搜索 2015 年的所有日期。如果这不起作用，请尝试：
SELECT * FROM myTab WHERE startDate like '%-%-15';
