blocks|key|1786350|text|您的意思是所选行的日期范围不应该完全在指定的日期范围内？在这种情况下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1786351|select+*
from+test_table
where+start_date+<+date+'2009-12-15'
or+end_date+>+date+'2010-01-02';|code-block|syntax|javascript|1786352|(以上语法用于Oracle，您的语法可能略有不同)。|1786353|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Do you mean that the date range of the selected rows should not lie fully within the specified date range? In which case:

<pre><code>select *
from test_table
where start_date &lt; date '2009-12-15'
or end_date &gt; date '2010-01-02';
</code></pre>

(Syntax above is for Oracle, yours may differ slightly).

blocks|key|487257|text|假设start_date在end_date之前，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|487258|interval+start_date..end_date不是在两个日期之间，简单地说，它要么在2009-12-15之前开始，要么在2010-01-02之后结束。|487259|然后你可以简单地做|487260|start_date<CAST('2009-12-15'+AS+DATE)+or+end_date>CAST('2010-01-02'+AS+DATE)|code-block|syntax|javascript|487261|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Assuming that start_date is before 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.

Then you can simply do 

<pre><code>start_date&lt;CAST('2009-12-15' AS DATE) or end_date&gt;CAST('2010-01-02' AS DATE)
</code></pre>

blocks|key|486871|text|您当前要做的是检查start_date和end_date是否都不在给定的日期范围内。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|486872|我猜您真正要找的是一个不符合给定日期范围的记录。如果是，请使用下面的查询。|486873|SELECT+*+
++++FROM+`test_table`+
++++WHERE++CAST('2009-12-15'+AS+DATE)+>+start_date++AND++CAST('2010-01-02'+AS+DATE)+<+end_date|code-block|syntax|javascript|486874|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

What you are currently doing is checking whether neither the start_date nor the end_date fall within the range of the dates given.

I guess what you are really looking for is a record which does not fit in the date range given. If so, use the query below.

<pre><code>SELECT * 
 FROM `test_table` 
 WHERE CAST('2009-12-15' AS DATE) &gt; start_date AND CAST('2010-01-02' AS DATE) &lt; end_date
</code></pre>

blocks|key|1786380|text|你的逻辑是倒退的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1786381|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)|code-block|syntax|javascript|1786382|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Your logic is backwards.

<pre><code>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)
</code></pre>

blocks|key|1848164|text|试试看：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1848165|select+*+from+'test_table'
where+end_date+<+CAST('2009-12-15'+AS+DATE)
or+start_date+>+CAST('2010-01-02'+AS+DATE)|code-block|syntax|javascript|1848166|这将返回与您的日期范围完全不重叠的所有日期范围。|1848167|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

How about trying:

<pre><code>select * from 'test_table'
where end_date &lt; CAST('2009-12-15' AS DATE)
or start_date &gt; CAST('2010-01-02' AS DATE)
</code></pre>

which will return all date ranges which do not overlap your date range at all.

blocks|key|486921|text|为了出现重叠，表的start_date必须小于间隔结束日期(即，它必须在间隔结束之前开始)，并且表的end_date必须大于间隔开始日期。根据您的需求，您可能需要使用<=和>=。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|486922|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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 &lt;= and &gt;= depending on your requirements.

blocks|key|486967|text|如果'NOT‘放在start_date之前，它应该可以工作。出于某种原因(我不知道为什么)，当'NOT‘放在'BETWEEN’之前时，它似乎会返回所有内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|486968|NOT+(start_date+BETWEEN+CAST('2009-12-15'+AS+DATE)+AND+CAST('2010-01-02'+AS+DATE))|code-block|syntax|javascript|486969|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>NOT (start_date BETWEEN CAST('2009-12-15' AS DATE) AND CAST('2010-01-02' AS DATE))
</code></pre>

I need some help with SQL Query. 

I am trying to select all records from table <code>test_table</code> which would not fit between two dates '2009-12-15' and '2010-01-02'.

This is my table structure:

<pre><code>`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'
</code></pre>

My query:

<pre><code>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
</code></pre>

Any idea why my query select wrong records? Should I change the order of values in query to something like: 

<pre><code>start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
</code></pre>

Thanks a lot for any help

SQL Query NOT Between Two Dates

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要一些SQL查询的帮助。我正在尝试从表test_table中选择不适合在两个日期'2009-12-15‘和'2010-01-02’之间的所有记录。这是我的表结构：`start_date` date NOT NULL default '0000-00-00',`end_date` date NOT NULL defa...

问SQL查询不在两个日期之间
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询不在两个日期之间EN