SQL Query NOT Between Two Dates

Question

Hello all,

I need some help with SQL Query.

I am trying to select all records from table test_table which would not fit between two dates '2009-12-15' and '2010-01-02'.

This is my table structure:

`start_date` date NOT NULL default '0000-00-00',
`end_date` date NOT NULL default '0000-00-00'

-----------------------------
 **The following record should not be selected:**

`start_date`, `end_date`
'2003-06-04', '2010-01-01'

My query:

SELECT * 

FROM `test_table` 
WHERE 

CAST('2009-12-15' AS DATE) NOT BETWEEN start_date and end_date 
AND 
CAST('2010-01-02' AS DATE) NOT BETWEEN start_date and end_date

Any idea why my query select wrong records? Should I change the order of values in query to something like:

start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)

Thanks a lot for any help

Answer 1

Do you mean that the date range of the selected rows should not lie fully within the specified date range? In which case:

select *
from test_table
where start_date < date '2009-12-15'
or end_date > date '2010-01-02';

(Syntax above is for Oracle, yours may differ slightly).

Answer 2

Assuming that start_date is before end_date,

interval [start_date..end_date] NOT BETWEEN two dates simply means that either it starts before 2009-12-15 or it ends after 2010-01-02.

Then you can simply do

start_date<CAST('2009-12-15' AS DATE) or end_date>CAST('2010-01-02' AS DATE)

Answer 3

What you are currently doing is checking whether neither the start_date nor the end_date fall within the range of the dates given.

I guess what you are really looking for is a record which does not fit in the date range given. If so, use the query below.

SELECT * 
    FROM `test_table` 
    WHERE  CAST('2009-12-15' AS DATE) > start_date  AND  CAST('2010-01-02' AS DATE) < end_date

Answer 4

Your logic is backwards.

SELECT 
    *
FROM 
    `test_table`
WHERE
        start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
    AND end_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)

Answer 5

How about trying:

select * from 'test_table'
where end_date < CAST('2009-12-15' AS DATE)
or start_date > CAST('2010-01-02' AS DATE)

which will return all date ranges which do not overlap your date range at all.

Answer 6

For there to be an overlap the table's start_date has to be LESS THAN the interval end date (i.e. it has to start before the end of the interval) AND the table's end_date has to be GREATER THAN the interval start date. You may need to use <= and >= depending on your requirements.