SQL Oracle 日期“之间”查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2369222/
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 "Between" Query
提问by Gnaniyar Zubair
I am using oracle database. i want to execute one query to check the data between two dates.
我正在使用oracle数据库。我想执行一个查询来检查两个日期之间的数据。
NAME START_DATE
------------- -------------
Small Widget 15-JAN-10 04.25.32.000000 PM
Product 1 17-JAN-10 04.31.32.000000 PM
select * from <TABLENAME> where start_date
BETWEEN '15-JAN-10' AND '17-JAN-10'
But I dont get any results from above query. I think I have to use "like" and "%". But I dont know where to use them. Please throw some lights on this.
但是我没有从上面的查询中得到任何结果。我想我必须使用“like”和“%”。但我不知道在哪里使用它们。请在这点上点亮一些灯。
thanks in advance.
提前致谢。
回答by APC
Judging from your output it looks like you have defined START_DATE as a timestamp. If it were a regular date Oracle would be able to handle the implicit conversion. But as it isn't you need to explicitly cast those strings to be dates.
从您的输出来看，您似乎已将 START_DATE 定义为时间戳。如果是常规日期，Oracle 将能够处理隐式转换。但实际上，您不需要将这些字符串显式转换为日期。
SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
2 /
Session altered.
SQL>
SQL> select * from t23
2 where start_date between '15-JAN-10' and '17-JAN-10'
3 /
no rows selected
SQL> select * from t23
2 where start_date between to_date('15-JAN-10') and to_date('17-JAN-10')
3 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
SQL>
But we still only get one row. This is because START_DATE has a time element. If we don't specify the time component Oracle defaults it to midnight. That is fine for the fromside of the BETWEENbut not for the untilside:
但我们仍然只有一排。这是因为 START_DATE 有一个时间元素。如果我们不指定时间组件，Oracle 会将其默认为午夜。这适用于from 的一侧，BETWEEN但不适用于直到的一侧：
SQL> select * from t23
2 where start_date between to_date('15-JAN-10')
3 and to_date('17-JAN-10 23:59:59')
4 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
Product 1 17-JAN-10 04.31.32.000
SQL>
edit
编辑
If you cannot pass in the time component there are a couple of choices. One is to change the WHERE clause to remove the time element from the criteria:
如果您无法传入时间组件，则有几种选择。一种是更改 WHERE 子句以从条件中删除时间元素：
where trunc(start_date) between to_date('15-JAN-10')
and to_date('17-JAN-10')
This might have an impact on performance, because it disqualifies any b-tree index on START_DATE. You would need to build a function-based index instead.
这可能会对性能产生影响，因为它取消了 START_DATE 的任何 b 树索引的资格。您需要改为构建基于函数的索引。
Alternatively you could add the time element to the date in your code:
或者，您可以将时间元素添加到代码中的日期：
where start_date between to_date('15-JAN-10')
and to_date('17-JAN-10') + (86399/86400)
Because of these problems many people prefer to avoid the use of betweenby checking for date boundaries like this:
由于这些问题，许多人更愿意between通过检查这样的日期边界来避免使用：
where start_date >= to_date('15-JAN-10')
and start_date < to_date('18-JAN-10')
回答by Chad Birch
You need to convert those to actual dates instead of strings, try this:
您需要将它们转换为实际日期而不是字符串，试试这个：
SELECT *
FROM <TABLENAME>
WHERE start_date BETWEEN TO_DATE('2010-01-15','YYYY-MM-DD') AND TO_DATE('2010-01-17', 'YYYY-MM-DD');
Edited to deal with format as specified:
编辑以处理指定的格式：
SELECT *
FROM <TABLENAME>
WHERE start_date BETWEEN TO_DATE('15-JAN-10','DD-MON-YY') AND TO_DATE('17-JAN-10','DD-MON-YY');
回答by Darren Atkinson
As APC rightly pointed out, your start_date column appears to be a TIMESTAMP but it could be a TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE datatype too. These could well influence any queries you were doing on the data if your database server was in a different timezone to yourself. However, let's keep this simple and assume you are in the same timezone as your server. First, to give you the confidence, check that the start_date is a TIMESTAMP data type.
正如 APC 正确指出的那样，您的 start_date 列似乎是 TIMESTAMP，但它也可能是 TIMESTAMP WITH LOCAL TIMEZONE 或 TIMESTAMP WITH TIMEZONE 数据类型。如果您的数据库服务器与您在不同的时区，这些很可能会影响您对数据进行的任何查询。但是，让我们保持简单并假设您与服务器处于同一时区。首先，为了给您信心，请检查 start_date 是否为 TIMESTAMP 数据类型。
Use the SQLPlus DESCRIBE command (or the equivalent in your IDE) to verify this column is a TIMESTAMP data type.
使用 SQLPlus DESCRIBE 命令（或 IDE 中的等效命令）验证此列是否为 TIMESTAMP 数据类型。
eg
例如
DESCRIBE mytable
描述 mytable
Should report :
应该报告：
Name Null? Type
----------- ----- ------------
NAME VARHAR2(20)
START_DATE TIMESTAMP
If it is reported as a Type = TIMESTAMP then you can query your date ranges with simplest TO_TIMESTAMP date conversion, one which requires no argument (or picture).
如果它被报告为 Type = TIMESTAMP 那么你可以用最简单的 TO_TIMESTAMP 日期转换来查询你的日期范围，一个不需要参数（或图片）。
We use TO_TIMESTAMP to ensure that any index on the START_DATE column is considered by the optimizer. APC's answer also noted that a function based index could have been created on this column and that would influence the SQL predicate but we cannot comment on that in this query. If you want to know how to find out what indexes have been applied to table, post another question and we can answer that separately.
我们使用 TO_TIMESTAMP 来确保优化器考虑 START_DATE 列上的任何索引。APC 的回答还指出，可以在此列上创建基于函数的索引，这会影响 SQL 谓词，但我们无法在此查询中对此进行评论。如果您想知道如何找出已应用于表的索引，请发布另一个问题，我们可以单独回答。
So, assuming there is an index on start_date, which is a TIMESTAMP datatype and you want the optimizer to consider it, your SQL would be :
因此，假设 start_date 上有一个索引，它是 TIMESTAMP 数据类型，并且您希望优化器考虑它，您的 SQL 将是：
select * from mytable where start_date between to_timestamp('15-JAN-10') AND to_timestamp('17-JAN-10')+.9999999
+.999999999 is very close to but isn't quite 1 so the conversion of 17-JAN-10 will be as close to midnight on that day as possible, therefore you query returns both rows.
+.999999999 非常接近但不是 1，因此 17-JAN-10 的转换将尽可能接近当天的午夜，因此您的查询将返回两行。
The database will see the BETWEEN as from 15-JAN-10 00:00:00:0000000to 17-JAN-10 23:59:59:99999and will therefore include all dates from 15th,16th and 17th Jan 2010 whatever the time component of the timestamp.
数据库将看到 BETWEEN 为从15-JAN-10 00:00:00:0000000到17-JAN-10 23:59:59:99999，因此将包括 2010 年 1 月 15、16 日和 17 日的所有日期，无论什么时间时间戳的组成部分。
Hope that helps.
希望有帮助。
Dazzer
达泽
回答by Darren Atkinson
Date Between Query
查询之间的日期
SELECT *
FROM emp
WHERE HIREDATE between to_date (to_char(sysdate, 'yyyy') ||'/09/01', 'yyyy/mm/dd')
AND to_date (to_char(sysdate, 'yyyy') + 1|| '/08/31', 'yyyy/mm/dd');
回答by Ajay
Following query also can be used:
也可以使用以下查询：
select *
from t23
where trunc(start_date) between trunc(to_date('01/15/2010','mm/dd/yyyy')) and trunc(to_date('01/17/2010','mm/dd/yyyy'))
