SQL NOT BETWEEN query

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0