SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a date when it's between two dates

    Hei!

    I'm developing an event calender and I got into trouble when I would like to do a search based on dates.

    The table 'calender' has two colums named 'startTime' and 'endTime'. Both is type DATETIME.

    The values of these columns are i.e. startTime=2002-09-20 15:00:00 and endTime=2002-09-22 18:00:00.

    If I want do a search on an event on 2002-09-21, how would the SELECT-statement be?

    I've tried something like this, with no success:
    PHP Code:
    // $day = 21;
    // $month = 9;
    // $year = 2002;

    SELECT 
    FROM calender 
    WHERE 
    (
       
    DAYOFMONTH(startTime) < $day
       
    AND MONTH(startTime) < $month 
       
    AND YEAR(startTime) < $year
       
    ) AND (
       
    DAYOFMONTH(endTime) > $day 
       
    AND MONTH(endTime) > $month 
       
    AND YEAR(endTime) > $year
       

    Any help is very appreciated!

    -Helge

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    select *
    from calendar
    where '2002-09-21' between
    date_format(startTime, '%Y-%m-%d') and
    date_format(endTime, '%Y-%m-%d')


    An alternative to date_format() is cast() or convert()
    (If version >= MySQL 4.0.2)
    Last edited by jofa; Sep 18, 2002 at 05:35.

  3. #3
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or even easier, if you're after a specific day:

    PHP Code:
    SELECT *  
    FROM calender  
    WHERE 

       
    date_format(startTime'%Y-%m-%d') = '2002-09-21'
       
    ) OR ( 
       
    date_format(endTime'%Y-%m-%d') = '2002-09-21'
       

    G

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by greg.harvey
    Or even easier, if you're after a specific day:

    PHP Code:
    SELECT *  
    FROM calender  
    WHERE 

       
    date_format(startTime'%Y-%m-%d') = '2002-09-21'
       
    ) OR ( 
       
    date_format(endTime'%Y-%m-%d') = '2002-09-21'
       

    G
    That query will not return events starting before 2002-09-21 and ending after 2002-09-21...

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hei

    Thank you for trying to helping me out. I've tried different things with your suggestion jofa. But nothing seems to work.

    My where clause i larger then I first told you, so here it what it look like now.
    PHP Code:
    SELECT *
    FROM table1table2
    WHERE table1
    .col1 table2.col2 AND 
        (
    2002-9-21 BETWEEN 
         DATE_FORMAT
    (startTime'%Y-%c-%e') AND 
         
    DATE_FORMAT(endTime'%Y-%c-%e')) 
    The difference from what you suggested is that I have added paranthesis around the 'problem part' of the where clause and have changed the format string in the DATE_FORMAT function.
    %m - Month, numeric (01..12)
    %c - Month, numeric (1..12)
    %d - Day of the month, numeric (00..31)
    %e - Day of the month, numeric (0..31)

    Do anybody have any suggestion on how I maybe solve this problem?

    -Helge

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think '%Y-%m-%d' is the correct format to use, otherwise '2002-9-21' will be greater than '2002-10-21'

    Don't forget the quotes!
    '2002-09-21', not 2002-09-21


    SELECT *
    FROM table1, table2
    WHERE table1.col1 = table2.col2 AND
    ('2002-09-21' BETWEEN
    DATE_FORMAT(startTime, '%Y-%m-%d') AND
    DATE_FORMAT(endTime, '%Y-%m-%d'))

  7. #7
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query...
    SELECT *
    FROM table1, table2
    WHERE table1.col1 = table2.col2 AND
    (2002-09-21 BETWEEN
    DATE_FORMAT(startTime, '%Y-%m-%d') AND
    DATE_FORMAT(endTime, '%Y-%m-%d'))
    ...is not badly formatted sql,
    the problem is it's equal to...
    SELECT *
    FROM table1, table2
    WHERE table1.col1 = table2.col2 AND
    (1972 BETWEEN
    DATE_FORMAT(startTime, '%Y-%m-%d') AND
    DATE_FORMAT(endTime, '%Y-%m-%d'))


  8. #8
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, jofa!

    That worked perfectly!

    I didn't know about this
    I think '%Y-%m-%d' is the correct format to use, otherwise '2002-9-21' will be greater than '2002-10-21'
    And forgetting the quotes: No good.

    -Helge


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
  •