Select statement for data with same month and year

[[deleted]]

I'm trying to pull data out of my mySQL database based on the month and year of the entries. I'm using a date field.

This query will be dynamic base on the current month and year.

As a test I've tried:

$result = mysql_query ("SELECT DISTINCT i.racedate,r.racename FROM crc1.race r, crc1.raceindex i
                  WHERE r.raceid = i.race_id
                  AND date_format(i.racedate,'%M') as rmonth = '08'
                  AND date_format(i.racedate,'%Y') as ryear = '2003'");

This has not worked and I'm not sure where to go from here.

Any suggestions?

[YappyDog]

The MySql manual says that the %M will return the month name, like "August", not "08".

http://www.mysql.com/doc/en/Date_and_time_functions.html

Looks like you need to use %m with a lower case "m" instead.

[[deleted]]

%M is a typo on my part. I used %m and %c instead.

[YappyDog]

What kind of output do you get when you run this:

SELECT date_format(i.racedate,'%m %Y') as mydate
FROM crc1.race r, crc1.raceindex i
WHERE r.raceid = i.race_id

[[deleted]]

I get the following:

04 2003
08 2003

[YappyDog]

Well then, either one of these should work..........

SELECT DISTINCT i.racedate,r.racename
FROM crc1.race r, crc1.raceindex i
WHERE r.raceid = i.race_id
AND date_format(i.racedate,'%m %Y') = '08 2003'

SELECT DISTINCT i.racedate,r.racename
FROM crc1.race r, crc1.raceindex i
WHERE r.raceid = i.race_id
AND date_format(i.racedate,'%m') = '08'
AND date_format(i.racedate,'%Y') = '2003'