where 子句中的 Oracle 日期比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43502875/
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 date comparison in where clause
提问by Hitesh Joshi
For eg I have a student table with a DOJ(date of joining) column with its type set as DATE now in that I have stored records in dd-mon-yy format.
例如，我有一个带有 DOJ（加入日期）列的学生表，现在它的类型设置为 DATE，因为我已经以 dd-mon-yy 格式存储了记录。
I have an IN param at runtime with date passed as string and its in dd/mm/yyyy format. How do I compare and fetch results on date?
我在运行时有一个 IN 参数，日期作为字符串传递，其格式为 dd/mm/yyyy。如何比较和获取日期结果？
I want to fetch count of records of students who have DOJ of 25-AUG-92 per my database table student, but I am getting date as varchar in dd/mm/yyyy format in an IN param, kindly please guide.
我想为每个我的数据库表学生获取 DOJ 为 25-AUG-92 的学生的记录计数，但我在 IN 参数中以 dd/mm/yyyy 格式获取日期为 varchar，请指导。
I have tried multiple options such as trunc, to_date, to_charbut, unfortunately nothing seems to work.
我尝试了多个选项，例如trunc, to_date，to_char但不幸的是似乎没有任何效果。
回答by MT0
I have a student table with a DOJ(date of joining) column with its type set as
DATEnow in that I have stored records indd-mon-yyformat.
我有一个带有 DOJ（加入日期）列的学生表，其类型设置为
DATE现在，因为我已按dd-mon-yy格式存储记录。
Not quite, the DATEdata-type does not have a format; it is stored internally in tables as 7-bytes(year is 2 bytes and month, day, hour, minute and second are 1-byte each). The user interface you are using (i.e. SQL/PLUS, SQL Developer, Toad, etc.) will handle the formatting of a DATEfrom its binary format to a human readable format. In SQL/Plus (or SQL Developer) this format is based on the NLS_DATE_FORMATsession parameter.
不完全是，DATE数据类型没有格式；它以7 个字节的形式在内部存储在表中（年为 2 个字节，月、日、时、分和秒各为 1 个字节）。您正在使用的用户界面（即 SQL/PLUS、SQL Developer、Toad 等）将处理DATE从二进制格式到人类可读格式的格式。在 SQL/Plus（或 SQL Developer）中，这种格式基于NLS_DATE_FORMAT会话参数。
If the DATEis input using only the day, month and year then the time component is (probably) going to be set to 00:00:00(midnight).
如果DATE仅使用日、月和年输入，则时间组件（可能）将设置为00:00:00（午夜）。
I have an IN param at runtime with date passed as string or say varchar and its in
dd/mm/yyyyformat. How do I compare and fetch results on date.?
我在运行时有一个 IN 参数，日期作为字符串传递，或者说 varchar 及其
dd/mm/yyyy格式。我如何比较和获取日期的结果。？
Assuming the time component for you DOJ column is always midnight then:
假设您 DOJ 专栏的时间部分始终是午夜，那么：
SELECT COUNT(*)
FROM students
WHERE doj = TO_DATE( your_param, 'dd/mm/yyyy' )
If it isn't always midnight then:
如果不总是午夜，那么：
SELECT COUNT(*)
FROM students
WHERE TRUNC( doj ) = TO_DATE( your_param, 'dd/mm/yyyy' )
or:
或者：
SELECT COUNT(*)
FROM students
WHERE doj >= TO_DATE( your_param, 'dd/mm/yyyy' )
AND doj < TO_DATE( your_param, 'dd/mm/yyyy' ) + INTERVAL '1' DAY
回答by Nick
The below shoulddo what you've described. If not, provide more information on how "nothing seems to work".
下面应该做你所描述的。如果没有，请提供有关“似乎没有任何效果”的更多信息。
-- Get the count of students with DOJ = 25-AUG-1992
SELECT COUNT(1)
FROM STUDENT
WHERE TRUNC(DOJ) = TO_DATE('25/AUG/1992','dd/mon/yyyy');
The above was pulled from this answer. You may want to look at the answer, because if performance is critical to you, there is a different way to write this query which doesn't use trunc, which will allow Oracle to use index on DOJ, if one is present.
以上是从这个答案中提取的。您可能想查看答案，因为如果性能对您至关重要，则有一种不同的方式来编写此查询，该查询不使用trunc，这将允许 Oracle 在 DOJ 上使用索引（如果存在）。
回答by Hitesh Joshi
Though I am bit late in posting this but I have been able to resolve this.
虽然我发布这个有点晚，但我已经能够解决这个问题。
What I did was I converted both the dates to_char in similar formats and it worked here is my query condition that worked..
我所做的是我将两个日期都转换为类似格式的 to_char 并且它在这里工作的是我的查询条件有效..
TO_CHAR(TO_DATE(C.DOB, 'DD-MON-YY'),'DD-MON-YY')=TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY'))
Thanks for the support all. :)
谢谢大家的支持。:)
