SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Date compare

  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date compare

    Hi I have the following table:

    PHP Code:
    descript start                           end
    test 1    
    2011-07-18 14:30:00  2011-07-18 17:00:00
    test 2    
    2011-07-18 00:00:00  2011-07-19 00:00:00 
    PHP Code:
    SELECT FROM event WHERE start >= "2011-07-18 00:00:00" AND end <= "2011-07-18 23:59:59"
    The above query is dynamically selected by user. Given this sql query I'd like to also retrieve the 2nd record as the start date falls into 18th July category. How do I do that?

    The above select statement obviously can't retrieve the 2nd result as the end date is more than "2011-07-18 23:59:59" but the start date is still in the 18july range. If i use an OR in the statement I get garbage result
    I Dunno LOL \(_o)/

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The start date has to be before the ending of the given period, and the end date after the start of the given period. That way you get all rows who are active in at least a part of the given period.
    Code:
    WHERE start <= "2011-07-18 23:59:59" 
    AND end >= "2011-07-18 00:00:00"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    The start date has to be before the ending of the given period, and the end date after the start of the given period. That way you get all rows who are active in at least a part of the given period.
    Code:
    WHERE start <= "2011-07-18 23:59:59" 
    AND end >= "2011-07-18 00:00:00"
    Thanks for the reply guido. Given this start <= "2011-07-18 23:59:59" , wouldn't it retrieve anything less than that? Say for example if I have a 2010 record?

    I just think it through the logic and it seems that when user select the date range in the calendar, it will always have a start date. Instead of comparing "in-between" I use this start date like this:

    start BETWEEN "2011-07-18 00:00:00" AND "2011-07-18 23:59:59"
    Do you think this is the right way? SO I dont care about the end date any more since the start is the one that really triggers the event range.
    I Dunno LOL \(_o)/

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Only if the end date is bigger than "2011-07-18 00:00:00"

    The AND in the WHERE clause means it has to satisfy both criteria.

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh hey thanks a million guido! It works perfectly now. Thanks x1000 very much!
    I Dunno LOL \(_o)/


Bookmarks

Posting Permissions

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