Date range/query problem..

Question

I have a table with 3 fields:

• id
• datestart
• dateend

I need to query this to find out if a pair of dates from a form are conflicting i.e

table entry 1, 2010-12-01, 2010-12-09

from the form 2010-12-08, 2010-12-15 (edited, typo)

select id 
  from date_table  
 where '2010-12-02' between datestart and dateend

That returns me the id that I want, but what I would like to do is to take the date range from the form and do a query similar to what I have got that will take both form dates 2010-12-08, 20-12-15 and query the db to ensure that there is no conflicting date ranges in the table.

Am sat scratching my head with the problem...

TIA

Answer 1

select id from date_table where '2010-12-02' between datestart and dateend;

I am not aware of the scope of your program. But, it seems like you're saying: select id from table where this date "2010-12-02" is in between 2 variables. I think you want to select where datestart is between the 2 variables.

Forgive me & correct me if I am wrong? You probably want something like this:

SELECT id FROM date_table WHERE datestart = datestart AND dateend = dateend;

if that's the case you probably just want a count:

SELECT COUNT(id) FROM date_table WHERE datestart = datestart AND dateend = dateend;

Good luck!

Answer 2

If I've understood your request correctly you would like a list of all entries in date_table that either overlaps your form_start, your form_end or is entirely contained within the form_start - form_end date range.

So your query should look something like this:

SELECT id FROM
date_table
WHERE
(datestart < form_start AND dateend => form_start)
OR
(datestart <= form_end AND dateend > form_end)
OR
(datestart >= form_start  AND dateend <= form_end)