SQL NOT BETWEEN查询 [英] SQL NOT BETWEEN query

问题描述

在此先感谢您的任何建议或提示！

Thanks in advance for any advice or tips!

我在mysql数据库table1中有一个预订表.它包含开始日期和结束日期. 我还有另一个表，table2，其中包含我需要获取的信息，但仅当特定日期不位于table1中任何行的任何日期之间时才包含.

I have a booking table in a mysql database, table1. It contains a start date and a finish date. I have another table, table2 which contains the information I need to get but only when a specific date does NOT reside between any of the dates from any rows in table1.

一个例子；

select table2.testfield
FROM table2, table1
WHERE '2011-02-24 18:00:00'
NOT BETWEEN table1.start 
AND table1.finish

但是我无法使它正常工作！有什么建议吗?

However I cannot get it to work! Any suggestions?

推荐答案

这应该可以，但看起来应该更像

This should work but should look something more like

select table2.testfield
FROM table2, table1
WHERE table1.YourField = '2011-02-24 18:00:00' 
AND
NOT BETWEEN table1.start AND table1.finish

这还假定您的table1.start和table1.finish字段是DateTime类型.如果不是，您可以尝试投射字段

This also presumes that your table1.start and table1.finish fields are of type DateTime. If they aren't you could try Casting the fields

select table2.testfield
    FROM table2, table1
    WHERE table1.YourField = '2011-02-24 18:00:00' 
    AND
    NOT BETWEEN Cast(table1.start as DateTime) AND Cast(table1.finish As DateTime)

编辑:看着您的问题，我意识到日期可能不是数据库值:)，因此您的方法应该可以使用，但是您可能需要将字符串转换为日期时间.

Edit Looking at your question I realized that the date probably isn't a database value :) so your method should work but you may need to cast the string to a datetime.

这篇关于SQL NOT BETWEEN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！