SQL 如何在 Oracle 查询中的两个给定日期之间选择日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5041151/
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
How do I select dates between two given dates in an Oracle query?
提问by Prabakaran
How do I select dates between two given dates in an Oracle query?
如何在 Oracle 查询中的两个给定日期之间选择日期？
回答by Greg Reynolds
SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
FROM all_objects
WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')
from
从
http://forums.devshed.com/oracle-development-96/select-all-dates-between-two-dates-92997.html
http://forums.devshed.com/oracle-development-96/select-all-dates-between-two-dates-92997.html
回答by diagonalbatman
SELECT * FROM your_table WHERE your_date_field BETWEEN DATE '2010-01-01' AND DATE '2011-01-01';
回答by Michael Broughton
You can use the LEVEL pseudocolumn in a tricky way to generate a series, so, for example, to get the list of days between today and 20 days from now I can:
您可以以一种巧妙的方式使用 LEVEL 伪列来生成一个系列，因此，例如，要获取从今天到 20 天之间的天数列表，我可以：
select trunc(sysdate+lvl) from
(select level lvl from dual connect by level < ((sysdate+20)-sysdate - 1) )
order by 1
Generically you can see how this would apply for any two given dates.
通常，您可以看到这如何适用于任何两个给定日期。
select trunc(early_date+lvl) from
(select level lvl from dual connect by level < (later_Date-early_date-1) )
order by 1
And you can adjust the clauses if you want to include the two end dates as well.
如果您还想包括两个结束日期，您可以调整子句。
回答by tbone
Use "between". In a general sense:
使用“之间”。一般意义上：
select * from someTable where dateCol between date1 and date2;
note that dateCol is defined as a date and date1 and date2 are also date values. If these aren't dates, then you'll convert them to dates using to_date function.
请注意，dateCol 被定义为日期，而 date1 和 date2 也是日期值。如果这些不是日期，那么您将使用 to_date 函数将它们转换为日期。
回答by vibs2006
This would be the best approach as also mentioned in PL SQL officialDocumentation.
这将是 PL SQL官方文档中也提到的最佳方法。
we have to use PL SQL inbuilt function TO_DATE(Date, Date format)to get the solution.
我们必须使用 PL SQL 内置函数TO_DATE(Date, Date format)来获得解决方案。
SELECT * FROM order_details
WHERE order_date
BETWEEN
TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND
TO_DATE ('2014/02/28', 'yyyy/mm/dd');
It is equivalentto the following statement using comparison operators
这相当于用比较操作符下面的语句
SELECT * FROM order_details
WHERE order_date >= TO_DATE('2014/02/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2014/02/28','yyyy/mm/dd');
回答by Ram Yerra
with all_days as (select trunc(to_date('12-03-2017','dd-mm-yyyy')+levl)-1 as all_dates from (select level levl from dual connect by level < (sysdate-to_date('12-03-2017','DD-MM-YYYY')+1) ) order by 1) select count(*) as no_of_days from all_days where ltrim(rtrim(to_char(all_dates,'DAY'))) not in ('SATURDAY','SUNDAY');
with all_days as (select trunc(to_date('12-03-2017','dd-mm-yyyy')+levl)-1 as all_dates from (select level levl from dual connect by level < (sysdate-to_date('12 -03-2017','DD-MM-YYYY')+1) ) order by 1) select count(*) as no_of_days from all_days where ltrim(rtrim(to_char(all_dates,'DAY'))) not in ('星期六星期天'）;
回答by Klaus Byskov Pedersen
select * from yourtable where somedate between sysdate and sysdate - 42
