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