SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member devan21's Avatar
    Join Date
    May 2014
    Location
    South Africa
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question retrieve data from MySQL between two date ranges

    hi all,

    I have to retrieve data based on two date ranges, please help, my current Query should retrieve all records but it displays no records?

    on submit, I have 2 date ranges in the $_POST data : $datefrom & $dateto in this format = 01-01-2014 ( dd/mm/yyyy )

    in the table I am trying to query, I have a column called e_date ( dd/mm/yyyy ) saved as a string.

    this is my query which is not retrieving any info nor generating any errors, I can't figure this one out, please help

    my query :
    Code:
    $query="SELECT * from MYTABLE where client_id=$client AND STR_TO_DATE(e_date, '%d/%m/%Y') BETWEEN STR_TO_DATE($datefrom, '%d-%m-%Y') AND STR_TO_DATE($dateto, '%d-%m-%Y')";

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,152
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Though you may feel more comfortable seeing dates in dd-mm-yyyy format, it isn't the best for code working with dates.
    Try yyyy-mm-dd instead.

  3. #3
    SitePoint Member devan21's Avatar
    Join Date
    May 2014
    Location
    South Africa
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Though you may feel more comfortable seeing dates in dd-mm-yyyy format, it isn't the best for code working with dates.
    Try yyyy-mm-dd instead.
    Hi,

    thanks for the info, before I change the date formats in the database, is there a way to make the current query work as is?

    thanks

  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)
    Quote Originally Posted by devan21 View Post
    ... is there a way to make the current query work as is?
    yes, but it will be ugly, complex, clumsy, and inefficient

    seriously, change your datatype to DATE if you possibly can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member devan21's Avatar
    Join Date
    May 2014
    Location
    South Africa
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, but it will be ugly, complex, clumsy, and inefficient

    seriously, change your datatype to DATE if you possibly can
    HI

    ok, I have changed the datatype to DATE and changed my query to :
    Code:
    $query="SELECT * from MYTABLE WHERE client_id=$client AND e_date BETWEEN $datefrom AND $dateto";
    but it still does not work, the default date format now is yyyy-mm-dd, but with this query, still no results? I also added dummy records with correct date format, still nothing?

  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)
    please repeat the query outside of php, and instead of using php variables, use actual from and to dates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member devan21's Avatar
    Join Date
    May 2014
    Location
    South Africa
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please repeat the query outside of php, and instead of using php variables, use actual from and to dates
    hi

    Ok, just fixed it now, it works 100%, I forgot to inclose the $datefrom & $dateto data with '', works like a charm now, thank you for your help, and from now on, will save dates as DATE types, not strings.


Tags for this Thread

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
  •