MySQL SQL NOT BETWEEN 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5106466/
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 NOT BETWEEN query
提问by steve
Thanks in advance for any advice or tips!
在此先感谢您的任何建议或提示！
I have a booking table in a mysql database, table1. It contains a start date and a finish date.
I have another table, table2which 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.
我在 mysql 数据库中有一个预订表，table1. 它包含一个开始日期和一个完成日期。我有另一个表，table2其中包含我需要获取的信息，但仅当特定日期不位于table1.
An example;
一个例子;
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?
但是我无法让它工作！有什么建议？
回答by msarchet
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
This also presumes that your table1.startand table1.finishfields are of type DateTime. If they aren't you could try Casting the fields
这也假定您的table1.start和table1.finish字段的类型为DateTime。如果不是，您可以尝试 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)
EditLooking 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.
编辑看着您的问题，我意识到日期可能不是数据库值 :) 所以您的方法应该可以工作，但您可能需要将字符串转换为日期时间。
回答by Jon Egerton
Something like this then?
那么像这样吗？
select table2.testfield
FROM table2, table1
WHERE
table1.start > convert(datetime,'2011-02-24 18:00:00')
or table1.finish < convert(datetime,'2011-02-24 18:00:00')
