SQL NOT BETWEEN query

Question

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

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?

Answer 1

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

Answer 2

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