SQL 查询不在两个日期之间

发布于 2024-08-10 19:12:15 字数 758 浏览 5 评论 0原文

我需要一些有关 SQL 查询的帮助。

我试图从表 test_table 中选择不适合两个日期“2009-12-15”和“2010-01-02”之间的所有记录。

这是我的表结构:

`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'

我的查询:

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

知道为什么我的查询选择错误的记录吗?我是否应该将查询中的值的顺序更改为:

start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)

非常感谢您的帮助

I need some help with SQL Query.

I am trying to select all records from table test_table which would not fit between two dates '2009-12-15' and '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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

桃酥萝莉 2024-08-17 19:12:15

尝试一下:

select * from 'test_table'
where end_date < CAST('2009-12-15' AS DATE)
or start_date > CAST('2010-01-02' AS DATE)

它将返回与您的日期范围完全不重叠的所有日期范围。

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.

獨角戲 2024-08-17 19:12:15

你的逻辑是倒退的。

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)

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)
雨落星ぅ辰 2024-08-17 19:12:15

如果“NOT”放在 start_date 之前,它应该可以工作。由于某种原因(我不知道为什么),当“NOT”放在“BETWEEN”之前时,它似乎会返回所有内容。

NOT (start_date BETWEEN CAST('2009-12-15' AS DATE) AND CAST('2010-01-02' AS DATE))

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 BETWEEN CAST('2009-12-15' AS DATE) AND CAST('2010-01-02' AS DATE))
无声静候 2024-08-17 19:12:15

您的意思是所选行的日期范围不应完全位于指定的日期范围内?在这种情况下:(

select *
from test_table
where start_date < date '2009-12-15'
or end_date > date '2010-01-02';

上面的语法适用于 Oracle,您的语法可能略有不同)。

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).

你与昨日 2024-08-17 19:12:15

您当前正在做的是检查 start_date 和 end_date 是否都不在给定日期的范围内。

我想您真正要寻找的是不适合给定日期范围的记录。如果是这样,请使用下面的查询。

SELECT * 
    FROM `test_table` 
    WHERE  CAST('2009-12-15' AS DATE) > start_date  AND  CAST('2010-01-02' AS DATE) < end_date

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.

SELECT * 
    FROM `test_table` 
    WHERE  CAST('2009-12-15' AS DATE) > start_date  AND  CAST('2010-01-02' AS DATE) < end_date
浅暮の光 2024-08-17 19:12:15

假设 start_date 在 end_date 之前,

间隔 [start_date..end_date] NOT BETWEEN 两个日期仅意味着它要么在 2009-12-15 之前开始,要么在 2010-01-02 之后结束。

然后你可以简单地做

start_date<CAST('2009-12-15' AS DATE) or end_date>CAST('2010-01-02' AS DATE)

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

start_date<CAST('2009-12-15' AS DATE) or end_date>CAST('2010-01-02' AS DATE)
谈场末日恋爱 2024-08-17 19:12:15

为了存在重叠,表的开始日期必须小于间隔结束日期(即它必须在间隔结束之前开始),并且表的结束日期必须大于间隔开始日期。根据您的要求,您可能需要使用 <= 和 >=。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文