SQL 查询不在两个日期之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1702420/
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
SQL Query NOT Between Two Dates
提问by Kelvin
I need some help with SQL Query.
我需要一些 SQL 查询方面的帮助。
I am trying to select all records from table test_tablewhich would not fit between two dates '2009-12-15' and '2010-01-02'.
我试图从表中选择test_table不适合两个日期“2009-12-15”和“2010-01-02”之间的所有记录。
This is my table structure:
这是我的表结构：
`start_date` date NOT NULL default '0000-00-00',
`end_date` date NOT NULL default '0000-00-00'
-----------------------------
**The following record should not be selected:**
`start_date`, `end_date`
'2003-06-04', '2010-01-01'
My query:
我的查询：
SELECT *
FROM `test_table`
WHERE
CAST('2009-12-15' AS DATE) NOT BETWEEN start_date and end_date
AND
CAST('2010-01-02' AS DATE) NOT BETWEEN start_date and end_date
Any idea why my query select wrong records? Should I change the order of values in query to something like:
知道为什么我的查询选择错误的记录吗？我应该将查询中的值顺序更改为：
start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
Thanks a lot for any help
非常感谢您的帮助
回答by Jim Lynn
How about trying:
试试怎么样：
select * from 'test_table'
where end_date < CAST('2009-12-15' AS DATE)
or start_date > CAST('2010-01-02' AS DATE)
which will return all date ranges which do not overlap your date range at all.
这将返回与您的日期范围完全不重叠的所有日期范围。
回答by Dereleased
Your logic is backwards.
你的逻辑是反的。
SELECT
*
FROM
`test_table`
WHERE
start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
AND end_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
回答by Tony Andrews
Do you mean that the date range of the selected rows should not lie fully within the specified date range? In which case:
您的意思是所选行的日期范围不应完全位于指定的日期范围内吗？在这种情况下：
select *
from test_table
where start_date < date '2009-12-15'
or end_date > date '2010-01-02';
(Syntax above is for Oracle, yours may differ slightly).
（以上语法适用于 Oracle，您的可能略有不同）。
回答by Johannes Rudolph
What you are currently doing is checking whether neither the start_date nor the end_date fall within the range of the dates given.
您目前正在做的是检查 start_date 和 end_date 是否都在给定的日期范围内。
I guess what you are really looking for is a record which does not fitin the date range given. If so, use the query below.
我想您真正要寻找的是不符合给定日期范围的记录。如果是这样，请使用下面的查询。
SELECT *
FROM `test_table`
WHERE CAST('2009-12-15' AS DATE) > start_date AND CAST('2010-01-02' AS DATE) < end_date
回答by brandonstrong
If the 'NOT' is put before the start_date it should work. For some reason (I don't know why) when 'NOT' is put before 'BETWEEN' it seems to return everything.
如果“NOT”放在 start_date 之前，它应该可以工作。出于某种原因（我不知道为什么），当“NOT”放在“BETWEEN”之前时，它似乎会返回所有内容。
NOT (start_date BETWEEN CAST('2009-12-15' AS DATE) AND CAST('2010-01-02' AS DATE))
回答by Jim Garrison
For there to be an overlap the table's start_date has to be LESS THAN the interval end date (i.e. it has to start before the end of the interval) AND the table's end_date has to be GREATER THAN the interval start date. You may need to use <= and >= depending on your requirements.
为了有重叠，表的 start_date 必须小于间隔结束日期（即它必须在间隔结束之前开始）并且表的 end_date 必须大于间隔开始日期。根据您的要求，您可能需要使用 <= 和 >=。
回答by yu_sha
Assuming that start_date is before end_date,
假设 start_date 在 end_date 之前，
interval [start_date..end_date] NOT BETWEEN two dates simply means that either it starts before 2009-12-15 or it ends after 2010-01-02.
interval [start_date..end_date] NOT BETWEEN两个日期只是意味着它要么在 2009-12-15 之前开始，要么在 2010-01-02 之后结束。
Then you can simply do
然后你可以简单地做
start_date<CAST('2009-12-15' AS DATE) or end_date>CAST('2010-01-02' AS DATE)
