SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not selecting correct dates in date range

    hello,

    i have these variable
    $StartDate = date('Y-m-d', strtotime($StartDate));
    $EndDate = date('Y-m-d', strtotime($EndDate));

    when i echo it out the dates are passing to the form processing as they should 1/26/2009-2/1/2009, but the results return from sql are not.

    $query = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), TIME_FORMAT(m.mdate,'%r') as time, m.lid, m.item, m.description, m.status, DATE_FORMAT(m.date_complete,'%m/%d/%Y'), a.assign, c.cost, h.hour, s.serviceby FROM gas_maint m left join assign a on m.aid=a.aid left join cost c on m.id=c.mid left join hours h on m.id=h.mid left join service s on m.sid=s.sid WHERE m.mdate >= '$StartDate' AND m.mdate <= '$EndDate' AND m.lid IN (" . $lid . ") AND (m.item LIKE '%$search%' OR m.description LIKE '%$search%')";
    $result = mysql_query($query) or die ("Could not run the query: " . mysql_error());

    it's only returning records up until 1/31/09, but it should've included records for 2/1/09 since that is the end date being pass.

    can someone help?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the problem lies here:
    Code:
    AND m.mdate <= '$EndDate'
    if $EndDate is '2009-02-01', this actually corresponds to '2009-02-01 00:00:00'

    in other words, the query is working correctly -- but not as you intended -- and will include any rows where m.mdate is midnight of Feb 1st, but no more for the rest of that day

    the best way to do this is to use the next day and inequality, i.e.
    Code:
    AND m.mdate < '2009-02-02'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well it doesn't even include that date...
    i have a date selector where it includes 6 days from the current so if the start date is 1/26/09 and the start date is 2/1/09 it's not even including the time..and it's not even returning any results that are in 2/1/09 the last date it returns is 1/31/09.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    which WHERE clause did you use, yours or mine?
    Code:
    CREATE TABLE test_datetimes
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , mdate DATETIME
    );
    INSERT INTO test_datetimes ( mdate ) VALUES
     ( '2009-01-26 01:01:01' )
    ,( '2009-01-27 01:01:01' )
    ,( '2009-01-28 01:01:01' )
    ,( '2009-01-29 01:01:01' )
    ,( '2009-01-30 01:01:01' )
    ,( '2009-01-31 01:01:01' )
    ,( '2009-02-01 01:01:01' )
    ,( '2009-02-02 01:01:01' )
    ;
    SELECT 'your query', m.* 
      FROM test_datetimes AS m
     WHERE m.mdate >= '2009-01-26' 
       AND m.mdate <= '2009-02-01'
    ;
    /*
    your query  id  mdate
    your query  1  2009-01-26 01:01:01
    your query  2  2009-01-27 01:01:01
    your query  3  2009-01-28 01:01:01
    your query  4  2009-01-29 01:01:01
    your query  5  2009-01-30 01:01:01
    your query  6  2009-01-31 01:01:01
    */
    SELECT 'my query', m.* 
      FROM test_datetimes AS m
     WHERE m.mdate >= '2009-01-26' 
       AND m.mdate  < '2009-02-02'
    ;
    /*
    my query  id  mdate
    my query  1  2009-01-26 01:01:01
    my query  2  2009-01-27 01:01:01
    my query  3  2009-01-28 01:01:01
    my query  4  2009-01-29 01:01:01
    my query  5  2009-01-30 01:01:01
    my query  6  2009-01-31 01:01:01
    my query  7  2009-02-01 01:01:01
    */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, i did this: m.mdate <= Date_add('$EndDate', interval 1 day) and it works.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it should be < , not <= , otherwise you will include the midnight rows from the next day

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •