Selecting all records from one month

using WHERE

[eggy ricardo]

Hiya guys

I am currently building a PHP/MySQL site, and am stuck creating a particular querey to do with and events calendar kind of thing.

I have a table on my database where one field is a datetime type field with the date and time of the event in.

What i am wanting to do is use the WHERE in my querey to select all records where the datetime field is in the month of October 2004 for example but i cant seem to figure out how to get this working.

Can someone please suggest how to do this.

Cheers

Richard

[dreamcatcher]

One way would be to use SUBSTRING to get the date and month, then build your query from there:

$query = mysql_query("SELECT *,SUBSTRING(datefield,0,4) as year, SUBSTRING(datefield,6,2) as month FROM table") or die(mysql_error());

while ($row = mysql_fetch_array($query))
{
if (($row['month']=='10') && ($row['year']=='2004'))
{
//do something
}
}

Or maybe you can use SUBSTRING in the WHERE clause? Not sure.

[wruk999]

For Month/Year specific, you should be able to use the built in mysql date time calcualtion functions.

SELECT * FROM events WHERE MONTH(eventtime) = 10 AND YEAR(eventtime) = 04

All from the manual page: [dev.mysql.com...]

HTH,
wruk999

[eggy ricardo]

Thanks Wruk - that worked perfectly. Thanks dreamcatcher also for your input!

Cheers guys

[wruk999]

No problem.

Glad you got it sorted :)

Cheers.