I have all my dates in mySQL database stored in the following format "23-02-2010".

What is the best way to build a search query using this format to search for items between 2 dates?

If dates were in this format "20100223" it would be easier, but im unsure how to go about it with this format?

cheers

    Well, the best thing would be to store the dates as DATE type columns, in which case such things become very easy. I'm assuming they are VARCHAR columns since that would be a non-standard date format? If so, then you could convert the strings to date types via the MySQL STR_TO_DATE() function. However, this would be inefficient as it would probably mean that each such query would require a complete table scan, as it could not use indexes on those fields to limit the search.

    $sql = "SELECT . . . WHERE STR_TO_DATE(col1, '%d-%m-%Y') BETWEEN '$date1' AND '$date2'";
    

    Again, if at all possible, I would recommend changing that column to a DATE type.

      Write a Reply...