how to compare based on Date with DATETIME field of MYSQL?

I am having a DATETIME field.. I want to search based on Date only. using below query:

select subTitle,count(alertid) as cnt,alertdate from alertmaster a,subscriptionmaster s where s开发者_StackOverflow中文版.subId=a.subId GROUP BY a.subId,alertDate

I get results grouped by date & time.. e.g.

xxx 4 2011-04-09 01:03:15 & xxx 1 2011-04-09 01:02:11 are shown as different results, which i dnt want. How to do this?

You can select date(alertdate) instead.

select subTitle,count(alertid) as cnt,date(alertdate) from alertmaster a,subscriptionmaster s where s.subId=a.subId GROUP BY a.subId,date(alertdate)

... GROUP BY a.subId, DATE(alertDate)

the DATE() function will extract just the date portion of a date/time field. More details here.