SQL NOT BETWEEN query

Question

Thanks in advance for any tips or advice!

I have a reservation table in mysql database, table1 . It contains the start date and end date. I have another table table2 that contains the information I need to get, but only when a specific date is NOT between any dates from any rows in table1 .

Example:

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

However, I can’t get it to work! Any suggestions?

Answer 1

This should work, but should look something 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 assumes that your table1.start and table1.finish are of type DateTime . If it is not, you can try Casting 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) 

Change In response to your question, I realized that the date is probably not a database value :), so your method should work, but you may need to pass a string to datetime.

Answer 2

Something like that?

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