SQL NOT BETWEEN query

mysql sql between
steve picture steve · Feb 24, 2011 · Viewed 35.6k times · Source

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

msarchet picture msarchet · Feb 24, 2011

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.

Related questions

generate days from date range

I would like to run a query like select ... as days where `date` is between '2010-01-20' and '2010-01-24' And return data like: days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24

Read More
MySQL "between" clause not inclusive?

If I run a query with a between clause, it seems to exclude the ending value. For example: select * from person where dob between '2011-01-01' and '2011-01-31' This gets all results with dob from …

Read More
Mysql: Selecting values between two columns

I'm trying to select a value between 2 columns. Here is my dataset id from to price 1 0.00 2.00 2.50 2 2.00 3.00 3.00 3 3.00 4.00 4.50 My goal, if I have a value of 2 is to select the line with the ID 1 (between from and to). So here is the …

Read More