Select month and year only from DATETIME value

[tonyawelch]

I'm working on an event calendar, which is crazy because I hate working with dates and times, but I thought this might help me overcome being intimidated by it all...

 

    * my MySQL server version : 5.0.75

 

    * the raw MySQL statement in question - Don't have one yet - here to get help with creating the WHERE statement for the query.

 

    * any errors that MySQL returns to the client - N/A

 

    * the table structure & column indexes of the relevant tables

 

CREATE TABLE IF NOT EXISTS `testevents` (

  `event_id` int(10) NOT NULL auto_increment,

  `user_id` int(10) NOT NULL,

  `event_title` varchar(80) NOT NULL,

  `event_date` datetime NOT NULL,

  `event_location` text NOT NULL,

  `event_description` text NOT NULL,

  `submitted` datetime NOT NULL,

  PRIMARY KEY  (`event_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4

 

    * a clear and concise description of what you want this statement to achieve

 

Events are entered into the database via a user form and formatted to go into a DATETIME field (so, datetime in table appears like so: YYYY-MM-DD HH:MM:SS).

 

I want the calendar to be setup so that the user can view all the events in the database for the selected month (using the year and month values), and alternatively all the events for a particular day (using year, month and day values).  Getting the data for a particular day is easy enough:

 

$caldate = "$year-$month-$daynum";
$var = mysql_query(SELECT * FROM testevents WHERE date(event_date) = '$caldate');

 

But is it possible to select data when only the month and year is provided with the day being any day of that month?  It obviously wouldn't be efficient for me to extract the year and month from every record before picking out the records that match the query, but I can't find a date() command that only tries to match the year and month.

 

 

    * a description of what it's currently doing that's not to your liking - n/a

    * a brief listing of the types of things you've attempted so far - everything I think of, I know won't work, so I came straight to the experts!

[luca200]

SELECT whatyouwant FROM table WHERE MONTH(event_date) = x and YEAR(event_date) = y