PL/SQL (Oracle) 中的日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1334979/
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
Date Range in PL/SQL (Oracle)
提问by Saobi
If I have table with a Date column called myDate, with values like 2009-08-25 09:00:09.0.
如果我有一个名为 的日期列的表myDate，其值类似于2009-08-25 09:00:09.0.
I want to select all rows for Aug 25, from 12:00:01 AMuntil 11:59:59 PMand NOTHING for Aug 26. Is it sufficient to simply use the condition:
我想选择8 月 25日的所有行，从上午 12:00:01到晚上11:59:59，而没有选择8 月 26 日的所有行。简单地使用条件是否足够：
where myDate between Date '2009-08-25' and Date '2009-08-26'
And I want to select all rows BEFORE Aug 25, not including Aug 25. Can I do:
我想在Aug 25之前选择所有行，不包括 8 月 25 日。我可以这样做：
where myDate < Date '2009-08-25'
回答by Vincent Malgrat
if you want data for the full day 25 and excluding all the 26, you need to remove the first second of the 26:
如果您想要全天 25 的数据并排除所有 26 天，则需要删除 26 天的第一秒：
where myDate >= Date '2009-08-25' and myDate < Date '2009-08-26'
or
或者
where myDate between Date '2009-08-25' and Date '2009-08-26' - interval '1' second
Update-- A little precision: In Oracle the DATEdatatype is used both for 'Date' types with or without time. If you're dealing with dates without time, it is usually stored with a 00:00 time segment, meaning midnight. Because of this, the first between (my_date between '2009-08-25' and '2009-08-26') will potentially select two full days.
更新——有点精确：在 Oracle 中，DATE数据类型用于带或不带时间的“日期”类型。如果您要处理没有时间的日期，则通常将其存储为 00:00 时间段，即午夜。因此， ( my_date between '2009-08-25' and '2009-08-26')之间的第一个可能会选择两天。
By removing the first second of the 26, you ensure that you won't inadvertently select rows from the 26.
通过删除 26 中的第一秒，您可以确保不会无意中从 26 中选择行。
回答by Charles Bretana
To get everything between Aug 25, at 12:00:01 AM until 11:59:59 PM, inclusive, try this:
要在 8 月 25 日上午 12:00:01 到晚上 11:59:59（含）之间获取所有内容，请尝试以下操作：
Where myDate Between to_Date('yyyymmddhh24miss', '20090825000001')
And to_Date('yyyymmddhh24miss', '20090825235959')
(Why are you excluding midnight on the 25th ?? That first second (00:00:00) is part of the 25th as well... )
（你为什么不包括 25 日的午夜？？第一秒（00:00:00）也是 25 日的一部分......）
To get everything before Aug 25, try this:
要在 8 月 25 日之前获得所有内容，请尝试以下操作：
Where myDate < to_Date('yyyymmdd', '20090825')
回答by Newbee so forgive me
This trunc the year but you get my gist:
这截断了一年，但你明白了我的要点：
SELECT (select trunc(sysdate - (SELECT TRUNC(SYSDATE) -
add_months(trunc(sysdate, 'YEAR'), -24)
FROM DUAL))from DUAL)+ (ROWNUM - 1) effective_date
FROM DUAL
CONNECT BY LEVEL <= (SELECT TRUNC(SYSDATE) - add_months(trunc(sysdate, 'YEAR'), -24)
FROM DUAL);
回答by berlebutch
SELECT *
FROM TABLE
WHERE myDate BETWEEN myDate ('08/25/2009', 'MM/DD/YYYY')
AND myDate ('08/26/2009', 'MM/DD/YYYY')
