Date range/query problem

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,开发者_C百科 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

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)

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!