Date range problems...Please have a look

Discussion in 'MySQL' started by reubenrd, Feb 5, 2012.

0
  1. #1
    Hello

    I have been working on a website which requires a user to be able to search between certain date ranges.

    I have a table which consists of two columns which are used for matching and comparing the dates from. Each user is able to submit a travel request and will be travelling within a certain time.

    For example:-

    date_from (The day the user is travelling)
    date_to (The day upto travelling, or also known as the last day of travel)

    date_from & date_to are also the names of the columns on the table.

    Now i would like the query to also pickup users which are travelling between these point of times, nothing less than the "date_from" and nothing greater than the "date_to". I have had several play around with queries and some return results and other do not. Now the ones which have returned results, also returns the result if the "date_to" is greater than one matched within the table, this should not be possible! Can somebody help me here.... I have given example of the kind of query i am working with.

    $sql = "SELECT travelling_details.*, user_table.* FROM travelling_details LEFT JOIN user_table ON travelling_details.user_id=user_table.id WHERE (date_from <= '$date1') and (date_to >= '$date2')";
    PHP:
    Some insight would be great thankyou!
     
    reubenrd, Feb 5, 2012 IP
  2. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #2
    Query seems okay. We could get better idea of what problem you're facing if you could give an example or may be what is wrong with the output of the current query you are using.
     
    mastermunj, Feb 6, 2012 IP
  3. inayah

    inayah Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Query seems OK..check date format from variable $date1 and $date2, default date format of database is 'YYYY-MM-DD'
     
    inayah, Feb 8, 2012 IP
  4. QZAX

    QZAX Greenhorn

    Messages:
    55
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    18
    #4
    it is the date format (without any doubt).
    the date format of the variables to not match with the default date format of mysql
     
    QZAX, Feb 8, 2012 IP
  5. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #5
    The date is as of midnight of that date (IOW, there's still 24 hours to go before the date changes) if you don't include a time. The date in the database may not be "mm/dd/yyyy 00:00:00". If you want the query to find all records up to that date_to, make the query look for dates less than "(mm/dd/yyyy + one day) 00:00:00", or make sure that your to dates (but not your from dates) in the database include the time 23:59:59.
     
    Rukbat, Feb 25, 2012 IP