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

User · Answer

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 lt CAST  2009-12-15  AS DATE  or end date gt CAST  2010-01-02  AS DATE

User · Answer

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

User · Answer

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  lt   and  gt   depending on your requirements

User · Answer

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   gt  start date  AND  CAST  2010-01-02  AS DATE   lt  end date

User · Answer

If the  NOT  is put before the start date it should work  For some reason  I don t know why  when  NOT  is put before  BETWEEN  it seems to return everything   NOT  start date BETWEEN CAST  2009-12-15  AS DATE  AND CAST  2010-01-02  AS DATE

User · Answer

How about trying   select   from  test table  where end date  lt  CAST  2009-12-15  AS DATE  or start date  gt  CAST  2010-01-02  AS DATE    which will return all date ranges which do not overlap your date range at all

User · Answer

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  lt  date  2009-12-15  or end date  gt  date  2010-01-02      Syntax above is for Oracle  yours may differ slightly

[sql] SQL Query NOT Between Two Dates

Examples related to sql

Examples related to between