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屋!