Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    165

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

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bla4free View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    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'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •