SQL Query NOT Between Two Dates

Question

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

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.