Checking to see if date exists between two columns...

bla4free · #1 (**permalink**) 06-10-10, 15:47

I have a table with the following data:

Code:

id | people_id | start_date | end_date
---+-----------+------------+----------
 1 |        27 | 10/01/2010 | 10/07/2010
 2 |        27 | 10/09/2010 | 10/12/2010
 3 |        27 | 11/03/2010 | 11/05/2010

What I'm trying to do is compare a user-entered date between the dates in the start_date and end_date columns. For example, if the user enters '10/03/2010', I want to be able to detect that the date already exists between the two columns, per row, just so I can detect duplicate entries.

I have this right now:

Code:

SELECT * FROM unavailability WHERE start_date >= '". format_date_db($start_date) ."' OR end_date <= '". format_date_db($start_date) ."' AND people_id = '$people_id'

but, I think this is checking all the columns, but not necessarily at the "row" level. Is there a way I can check dates that are between each date in each column per row? Thanks!

r937 · #2 (**permalink**) 06-10-10, 15:56

Quote:

Originally Posted by bla4free

... I think this is checking all the columns, but not necessarily at the "row" level.

no, sorry, that's not right

it is checking only the columns you specified, one row at a time

i have a feeling you might not be getting the results you expected because you've defined your table using VARCHAR columns instead of DATE columns

bla4free · #3 (**permalink**) 06-10-10, 16:23

I figured it out--stupid mistake.

Code:

SELECT * FROM unavailability WHERE start_date <= '". format_date_db($start_date) ."' AND end_date >= '". format_date_db($start_date) ."' AND people_id = '$people_id'

Internet Services

Web Development

Digital Marketing

Consumer Tech