SELECT with date conversion does not work


I am trying to Select from a database where the date is in the format of “March 2010”…

I have the following code:


    public function selectAllArchiveEventsByDate($date){

        $sql = "SELECT * FROM tbl_events 
        WHERE deleted = 0 AND 
        date_format('date', '%F-%Y') = '$date'";
        $result = mysql_query($sql);
        return $result;

Then on the page itself i do this:

      		$event = Event::selectAllArchiveEventsByDate($_GET['archive']);
      		$i = 0;
      		while($row = mysql_fetch_array($event)){

The $_GET[‘archive’] is in the querystring, a typical example is shown below:

You will see i don’t get any errors, so i don’t understand why it does not work. In my database the ‘date’ column has dates like this: 2010-03-26 10:30:00

Any ideas why the code does not work?


A couple issues:

  1. There is no %F format

  1. You are formatting the literal word “date” not the column because you put single quotes around it. Don’t put quotes around column names

  2. ‘%F-%Y’, if it worked the way you thought, would’ve been “March-2010” which is not equal to “March 2010”.

Try DATE_FORMAT(date, ‘%M %Y’)

  1. This is going to be a very inefficient query, too inefficient to run if the site becomes popular or the database grows to more than a couple hundred rows. You want to be able to compare the dates directly as comparing the result of a function applied to the column requires scanning every row to evaluate the function. Instead transform the date in PHP so that you can produce a query that just compares it to the date, with greater than and less than.


Thanks, well i will get this working for the time being as i need to set the site live, and will change it to the way you have suggested very soon :wink:

First things first, i have changed the code and i get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /domains/ on line 25

Referring to this line:

while($row = mysql_fetch_array($event)){

I tried to output the sql ‘or die($sql)’ and i got this:

SELECT * FROM tbl_events WHERE deleted = 0 AND DATE_FORMAT(date, ‘%M %Y’) = March 2010

Any ideas?

The “March 2010” part is a literal string and needs to be enclosed in single quotes


Thanks, that worked perfectly. I will change it soon.

Thanks again.

to be efficient, you need to change it as follows (example for march 2010):

WHERE `date` &gt;= '2010-03-01'
  AND `date`  &lt; '2010-04-01'

note “less than” on the upper bound, which is the first day of the following month

that way you don’t have to worry about february 29th and all that stuff :wink: